1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot._typing import E
  25from sqlglot.errors import ParseError
  26from sqlglot.helper import (
  27    AutoName,
  28    camel_to_snake_case,
  29    ensure_collection,
  30    ensure_list,
  31    seq_get,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39
  40class _Expression(type):
  41    def __new__(cls, clsname, bases, attrs):
  42        klass = super().__new__(cls, clsname, bases, attrs)
  43
  44        # When an Expression class is created, its key is automatically set to be
  45        # the lowercase version of the class' name.
  46        klass.key = clsname.lower()
  47
  48        # This is so that docstrings are not inherited in pdoc
  49        klass.__doc__ = klass.__doc__ or ""
  50
  51        return klass
  52
  53
  54class Expression(metaclass=_Expression):
  55    """
  56    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  57    context, such as its child expressions, their names (arg keys), and whether a given child expression
  58    is optional or not.
  59
  60    Attributes:
  61        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  62            and representing expressions as strings.
  63        arg_types: determines what arguments (child nodes) are supported by an expression. It
  64            maps arg keys to booleans that indicate whether the corresponding args are optional.
  65        parent: a reference to the parent expression (or None, in case of root expressions).
  66        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  67            uses to refer to it.
  68        comments: a list of comments that are associated with a given expression. This is used in
  69            order to preserve comments when transpiling SQL code.
  70        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  71            optimizer, in order to enable some transformations that require type information.
  72        meta: a dictionary that can be used to store useful metadata for a given expression.
  73
  74    Example:
  75        >>> class Foo(Expression):
  76        ...     arg_types = {"this": True, "expression": False}
  77
  78        The above definition informs us that Foo is an Expression that requires an argument called
  79        "this" and may also optionally receive an argument called "expression".
  80
  81    Args:
  82        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96        self._hash: t.Optional[int] = None
  97
  98        for arg_key, value in self.args.items():
  99            self._set_parent(arg_key, value)
 100
 101    def __eq__(self, other) -> bool:
 102        return type(self) is type(other) and hash(self) == hash(other)
 103
 104    @property
 105    def hashable_args(self) -> t.Any:
 106        return frozenset(
 107            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 108            for k, v in self.args.items()
 109            if not (v is None or v is False or (type(v) is list and not v))
 110        )
 111
 112    def __hash__(self) -> int:
 113        if self._hash is not None:
 114            return self._hash
 115
 116        return hash((self.__class__, self.hashable_args))
 117
 118    @property
 119    def this(self):
 120        """
 121        Retrieves the argument with key "this".
 122        """
 123        return self.args.get("this")
 124
 125    @property
 126    def expression(self):
 127        """
 128        Retrieves the argument with key "expression".
 129        """
 130        return self.args.get("expression")
 131
 132    @property
 133    def expressions(self):
 134        """
 135        Retrieves the argument with key "expressions".
 136        """
 137        return self.args.get("expressions") or []
 138
 139    def text(self, key) -> str:
 140        """
 141        Returns a textual representation of the argument corresponding to "key". This can only be used
 142        for args that are strings or leaf Expression instances, such as identifiers and literals.
 143        """
 144        field = self.args.get(key)
 145        if isinstance(field, str):
 146            return field
 147        if isinstance(field, (Identifier, Literal, Var)):
 148            return field.this
 149        if isinstance(field, (Star, Null)):
 150            return field.name
 151        return ""
 152
 153    @property
 154    def is_string(self) -> bool:
 155        """
 156        Checks whether a Literal expression is a string.
 157        """
 158        return isinstance(self, Literal) and self.args["is_string"]
 159
 160    @property
 161    def is_number(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a number.
 164        """
 165        return isinstance(self, Literal) and not self.args["is_string"]
 166
 167    @property
 168    def is_int(self) -> bool:
 169        """
 170        Checks whether a Literal expression is an integer.
 171        """
 172        if self.is_number:
 173            try:
 174                int(self.name)
 175                return True
 176            except ValueError:
 177                pass
 178        return False
 179
 180    @property
 181    def is_star(self) -> bool:
 182        """Checks whether an expression is a star."""
 183        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 184
 185    @property
 186    def alias(self) -> str:
 187        """
 188        Returns the alias of the expression, or an empty string if it's not aliased.
 189        """
 190        if isinstance(self.args.get("alias"), TableAlias):
 191            return self.args["alias"].name
 192        return self.text("alias")
 193
 194    @property
 195    def alias_column_names(self) -> t.List[str]:
 196        table_alias = self.args.get("alias")
 197        if not table_alias:
 198            return []
 199        return [c.name for c in table_alias.args.get("columns") or []]
 200
 201    @property
 202    def name(self) -> str:
 203        return self.text("this")
 204
 205    @property
 206    def alias_or_name(self) -> str:
 207        return self.alias or self.name
 208
 209    @property
 210    def output_name(self) -> str:
 211        """
 212        Name of the output column if this expression is a selection.
 213
 214        If the Expression has no output name, an empty string is returned.
 215
 216        Example:
 217            >>> from sqlglot import parse_one
 218            >>> parse_one("SELECT a").expressions[0].output_name
 219            'a'
 220            >>> parse_one("SELECT b AS c").expressions[0].output_name
 221            'c'
 222            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 223            ''
 224        """
 225        return ""
 226
 227    @property
 228    def type(self) -> t.Optional[DataType]:
 229        return self._type
 230
 231    @type.setter
 232    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 233        if dtype and not isinstance(dtype, DataType):
 234            dtype = DataType.build(dtype)
 235        self._type = dtype  # type: ignore
 236
 237    @property
 238    def meta(self) -> t.Dict[str, t.Any]:
 239        if self._meta is None:
 240            self._meta = {}
 241        return self._meta
 242
 243    def __deepcopy__(self, memo):
 244        copy = self.__class__(**deepcopy(self.args))
 245        if self.comments is not None:
 246            copy.comments = deepcopy(self.comments)
 247
 248        if self._type is not None:
 249            copy._type = self._type.copy()
 250
 251        if self._meta is not None:
 252            copy._meta = deepcopy(self._meta)
 253
 254        return copy
 255
 256    def copy(self):
 257        """
 258        Returns a deep copy of the expression.
 259        """
 260        new = deepcopy(self)
 261        new.parent = self.parent
 262        return new
 263
 264    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 265        if self.comments is None:
 266            self.comments = []
 267        if comments:
 268            self.comments.extend(comments)
 269
 270    def append(self, arg_key: str, value: t.Any) -> None:
 271        """
 272        Appends value to arg_key if it's a list or sets it as a new list.
 273
 274        Args:
 275            arg_key (str): name of the list expression arg
 276            value (Any): value to append to the list
 277        """
 278        if not isinstance(self.args.get(arg_key), list):
 279            self.args[arg_key] = []
 280        self.args[arg_key].append(value)
 281        self._set_parent(arg_key, value)
 282
 283    def set(self, arg_key: str, value: t.Any) -> None:
 284        """
 285        Sets arg_key to value.
 286
 287        Args:
 288            arg_key: name of the expression arg.
 289            value: value to set the arg to.
 290        """
 291        if value is None:
 292            self.args.pop(arg_key, None)
 293            return
 294
 295        self.args[arg_key] = value
 296        self._set_parent(arg_key, value)
 297
 298    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 299        if hasattr(value, "parent"):
 300            value.parent = self
 301            value.arg_key = arg_key
 302        elif type(value) is list:
 303            for v in value:
 304                if hasattr(v, "parent"):
 305                    v.parent = self
 306                    v.arg_key = arg_key
 307
 308    @property
 309    def depth(self) -> int:
 310        """
 311        Returns the depth of this tree.
 312        """
 313        if self.parent:
 314            return self.parent.depth + 1
 315        return 0
 316
 317    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 318        """Yields the key and expression for all arguments, exploding list args."""
 319        for k, vs in self.args.items():
 320            if type(vs) is list:
 321                for v in vs:
 322                    if hasattr(v, "parent"):
 323                        yield k, v
 324            else:
 325                if hasattr(vs, "parent"):
 326                    yield k, vs
 327
 328    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 329        """
 330        Returns the first node in this tree which matches at least one of
 331        the specified types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 336
 337        Returns:
 338            The node which matches the criteria or None if no such node was found.
 339        """
 340        return next(self.find_all(*expression_types, bfs=bfs), None)
 341
 342    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 343        """
 344        Returns a generator object which visits all nodes in this tree and only
 345        yields those that match at least one of the specified expression types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The generator object.
 353        """
 354        for expression, *_ in self.walk(bfs=bfs):
 355            if isinstance(expression, expression_types):
 356                yield expression
 357
 358    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 359        """
 360        Returns a nearest parent matching expression_types.
 361
 362        Args:
 363            expression_types: the expression type(s) to match.
 364
 365        Returns:
 366            The parent node.
 367        """
 368        ancestor = self.parent
 369        while ancestor and not isinstance(ancestor, expression_types):
 370            ancestor = ancestor.parent
 371        return t.cast(E, ancestor)
 372
 373    @property
 374    def parent_select(self) -> t.Optional[Select]:
 375        """
 376        Returns the parent select statement.
 377        """
 378        return self.find_ancestor(Select)
 379
 380    @property
 381    def same_parent(self) -> bool:
 382        """Returns if the parent is the same class as itself."""
 383        return type(self.parent) is self.__class__
 384
 385    def root(self) -> Expression:
 386        """
 387        Returns the root expression of this tree.
 388        """
 389        expression = self
 390        while expression.parent:
 391            expression = expression.parent
 392        return expression
 393
 394    def walk(self, bfs=True, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree.
 397
 398        Args:
 399            bfs (bool): if set to True the BFS traversal order will be applied,
 400                otherwise the DFS traversal will be used instead.
 401            prune ((node, parent, arg_key) -> bool): callable that returns True if
 402                the generator should stop traversing this branch of the tree.
 403
 404        Returns:
 405            the generator object.
 406        """
 407        if bfs:
 408            yield from self.bfs(prune=prune)
 409        else:
 410            yield from self.dfs(prune=prune)
 411
 412    def dfs(self, parent=None, key=None, prune=None):
 413        """
 414        Returns a generator object which visits all nodes in this tree in
 415        the DFS (Depth-first) order.
 416
 417        Returns:
 418            The generator object.
 419        """
 420        parent = parent or self.parent
 421        yield self, parent, key
 422        if prune and prune(self, parent, key):
 423            return
 424
 425        for k, v in self.iter_expressions():
 426            yield from v.dfs(self, k, prune)
 427
 428    def bfs(self, prune=None):
 429        """
 430        Returns a generator object which visits all nodes in this tree in
 431        the BFS (Breadth-first) order.
 432
 433        Returns:
 434            The generator object.
 435        """
 436        queue = deque([(self, self.parent, None)])
 437
 438        while queue:
 439            item, parent, key = queue.popleft()
 440
 441            yield item, parent, key
 442            if prune and prune(item, parent, key):
 443                continue
 444
 445            for k, v in item.iter_expressions():
 446                queue.append((v, item, k))
 447
 448    def unnest(self):
 449        """
 450        Returns the first non parenthesis child or self.
 451        """
 452        expression = self
 453        while type(expression) is Paren:
 454            expression = expression.this
 455        return expression
 456
 457    def unalias(self):
 458        """
 459        Returns the inner expression if this is an Alias.
 460        """
 461        if isinstance(self, Alias):
 462            return self.this
 463        return self
 464
 465    def unnest_operands(self):
 466        """
 467        Returns unnested operands as a tuple.
 468        """
 469        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 470
 471    def flatten(self, unnest=True):
 472        """
 473        Returns a generator which yields child nodes who's parents are the same class.
 474
 475        A AND B AND C -> [A, B, C]
 476        """
 477        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 478            if not type(node) is self.__class__:
 479                yield node.unnest() if unnest else node
 480
 481    def __str__(self) -> str:
 482        return self.sql()
 483
 484    def __repr__(self) -> str:
 485        return self._to_s()
 486
 487    def sql(self, dialect: DialectType = None, **opts) -> str:
 488        """
 489        Returns SQL string representation of this tree.
 490
 491        Args:
 492            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 493            opts: other `sqlglot.generator.Generator` options.
 494
 495        Returns:
 496            The SQL string.
 497        """
 498        from sqlglot.dialects import Dialect
 499
 500        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 501
 502    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 503        indent = "" if not level else "\n"
 504        indent += "".join(["  "] * level)
 505        left = f"({self.key.upper()} "
 506
 507        args: t.Dict[str, t.Any] = {
 508            k: ", ".join(
 509                v._to_s(hide_missing=hide_missing, level=level + 1)
 510                if hasattr(v, "_to_s")
 511                else str(v)
 512                for v in ensure_list(vs)
 513                if v is not None
 514            )
 515            for k, vs in self.args.items()
 516        }
 517        args["comments"] = self.comments
 518        args["type"] = self.type
 519        args = {k: v for k, v in args.items() if v or not hide_missing}
 520
 521        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 522        right += ")"
 523
 524        return indent + left + right
 525
 526    def transform(self, fun, *args, copy=True, **kwargs):
 527        """
 528        Recursively visits all tree nodes (excluding already transformed ones)
 529        and applies the given transformation function to each node.
 530
 531        Args:
 532            fun (function): a function which takes a node as an argument and returns a
 533                new transformed node or the same node without modifications. If the function
 534                returns None, then the corresponding node will be removed from the syntax tree.
 535            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 536                modified in place.
 537
 538        Returns:
 539            The transformed tree.
 540        """
 541        node = self.copy() if copy else self
 542        new_node = fun(node, *args, **kwargs)
 543
 544        if new_node is None or not isinstance(new_node, Expression):
 545            return new_node
 546        if new_node is not node:
 547            new_node.parent = node.parent
 548            return new_node
 549
 550        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 551        return new_node
 552
 553    @t.overload
 554    def replace(self, expression: E) -> E:
 555        ...
 556
 557    @t.overload
 558    def replace(self, expression: None) -> None:
 559        ...
 560
 561    def replace(self, expression):
 562        """
 563        Swap out this expression with a new expression.
 564
 565        For example::
 566
 567            >>> tree = Select().select("x").from_("tbl")
 568            >>> tree.find(Column).replace(Column(this="y"))
 569            (COLUMN this: y)
 570            >>> tree.sql()
 571            'SELECT y FROM tbl'
 572
 573        Args:
 574            expression: new node
 575
 576        Returns:
 577            The new expression or expressions.
 578        """
 579        if not self.parent:
 580            return expression
 581
 582        parent = self.parent
 583        self.parent = None
 584
 585        replace_children(parent, lambda child: expression if child is self else child)
 586        return expression
 587
 588    def pop(self: E) -> E:
 589        """
 590        Remove this expression from its AST.
 591
 592        Returns:
 593            The popped expression.
 594        """
 595        self.replace(None)
 596        return self
 597
 598    def assert_is(self, type_: t.Type[E]) -> E:
 599        """
 600        Assert that this `Expression` is an instance of `type_`.
 601
 602        If it is NOT an instance of `type_`, this raises an assertion error.
 603        Otherwise, this returns this expression.
 604
 605        Examples:
 606            This is useful for type security in chained expressions:
 607
 608            >>> import sqlglot
 609            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 610            'SELECT x, z FROM y'
 611        """
 612        assert isinstance(self, type_)
 613        return self
 614
 615    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 616        """
 617        Checks if this expression is valid (e.g. all mandatory args are set).
 618
 619        Args:
 620            args: a sequence of values that were used to instantiate a Func expression. This is used
 621                to check that the provided arguments don't exceed the function argument limit.
 622
 623        Returns:
 624            A list of error messages for all possible errors that were found.
 625        """
 626        errors: t.List[str] = []
 627
 628        for k in self.args:
 629            if k not in self.arg_types:
 630                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 631        for k, mandatory in self.arg_types.items():
 632            v = self.args.get(k)
 633            if mandatory and (v is None or (isinstance(v, list) and not v)):
 634                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 635
 636        if (
 637            args
 638            and isinstance(self, Func)
 639            and len(args) > len(self.arg_types)
 640            and not self.is_var_len_args
 641        ):
 642            errors.append(
 643                f"The number of provided arguments ({len(args)}) is greater than "
 644                f"the maximum number of supported arguments ({len(self.arg_types)})"
 645            )
 646
 647        return errors
 648
 649    def dump(self):
 650        """
 651        Dump this Expression to a JSON-serializable dict.
 652        """
 653        from sqlglot.serde import dump
 654
 655        return dump(self)
 656
 657    @classmethod
 658    def load(cls, obj):
 659        """
 660        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 661        """
 662        from sqlglot.serde import load
 663
 664        return load(obj)
 665
 666
 667IntoType = t.Union[
 668    str,
 669    t.Type[Expression],
 670    t.Collection[t.Union[str, t.Type[Expression]]],
 671]
 672ExpOrStr = t.Union[str, Expression]
 673
 674
 675class Condition(Expression):
 676    def and_(
 677        self,
 678        *expressions: t.Optional[ExpOrStr],
 679        dialect: DialectType = None,
 680        copy: bool = True,
 681        **opts,
 682    ) -> Condition:
 683        """
 684        AND this condition with one or multiple expressions.
 685
 686        Example:
 687            >>> condition("x=1").and_("y=1").sql()
 688            'x = 1 AND y = 1'
 689
 690        Args:
 691            *expressions: the SQL code strings to parse.
 692                If an `Expression` instance is passed, it will be used as-is.
 693            dialect: the dialect used to parse the input expression.
 694            copy: whether or not to copy the involved expressions (only applies to Expressions).
 695            opts: other options to use to parse the input expressions.
 696
 697        Returns:
 698            The new And condition.
 699        """
 700        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 701
 702    def or_(
 703        self,
 704        *expressions: t.Optional[ExpOrStr],
 705        dialect: DialectType = None,
 706        copy: bool = True,
 707        **opts,
 708    ) -> Condition:
 709        """
 710        OR this condition with one or multiple expressions.
 711
 712        Example:
 713            >>> condition("x=1").or_("y=1").sql()
 714            'x = 1 OR y = 1'
 715
 716        Args:
 717            *expressions: the SQL code strings to parse.
 718                If an `Expression` instance is passed, it will be used as-is.
 719            dialect: the dialect used to parse the input expression.
 720            copy: whether or not to copy the involved expressions (only applies to Expressions).
 721            opts: other options to use to parse the input expressions.
 722
 723        Returns:
 724            The new Or condition.
 725        """
 726        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 727
 728    def not_(self, copy: bool = True):
 729        """
 730        Wrap this condition with NOT.
 731
 732        Example:
 733            >>> condition("x=1").not_().sql()
 734            'NOT x = 1'
 735
 736        Args:
 737            copy: whether or not to copy this object.
 738
 739        Returns:
 740            The new Not instance.
 741        """
 742        return not_(self, copy=copy)
 743
 744    def as_(
 745        self,
 746        alias: str | Identifier,
 747        quoted: t.Optional[bool] = None,
 748        dialect: DialectType = None,
 749        copy: bool = True,
 750        **opts,
 751    ) -> Alias:
 752        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 753
 754    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 755        this = self.copy()
 756        other = convert(other, copy=True)
 757        if not isinstance(this, klass) and not isinstance(other, klass):
 758            this = _wrap(this, Binary)
 759            other = _wrap(other, Binary)
 760        if reverse:
 761            return klass(this=other, expression=this)
 762        return klass(this=this, expression=other)
 763
 764    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]):
 765        return Bracket(
 766            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 767        )
 768
 769    def isin(
 770        self,
 771        *expressions: t.Any,
 772        query: t.Optional[ExpOrStr] = None,
 773        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 774        copy: bool = True,
 775        **opts,
 776    ) -> In:
 777        return In(
 778            this=maybe_copy(self, copy),
 779            expressions=[convert(e, copy=copy) for e in expressions],
 780            query=maybe_parse(query, copy=copy, **opts) if query else None,
 781            unnest=Unnest(
 782                expressions=[
 783                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 784                ]
 785            )
 786            if unnest
 787            else None,
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def __lt__(self, other: t.Any) -> LT:
 816        return self._binop(LT, other)
 817
 818    def __le__(self, other: t.Any) -> LTE:
 819        return self._binop(LTE, other)
 820
 821    def __gt__(self, other: t.Any) -> GT:
 822        return self._binop(GT, other)
 823
 824    def __ge__(self, other: t.Any) -> GTE:
 825        return self._binop(GTE, other)
 826
 827    def __add__(self, other: t.Any) -> Add:
 828        return self._binop(Add, other)
 829
 830    def __radd__(self, other: t.Any) -> Add:
 831        return self._binop(Add, other, reverse=True)
 832
 833    def __sub__(self, other: t.Any) -> Sub:
 834        return self._binop(Sub, other)
 835
 836    def __rsub__(self, other: t.Any) -> Sub:
 837        return self._binop(Sub, other, reverse=True)
 838
 839    def __mul__(self, other: t.Any) -> Mul:
 840        return self._binop(Mul, other)
 841
 842    def __rmul__(self, other: t.Any) -> Mul:
 843        return self._binop(Mul, other, reverse=True)
 844
 845    def __truediv__(self, other: t.Any) -> Div:
 846        return self._binop(Div, other)
 847
 848    def __rtruediv__(self, other: t.Any) -> Div:
 849        return self._binop(Div, other, reverse=True)
 850
 851    def __floordiv__(self, other: t.Any) -> IntDiv:
 852        return self._binop(IntDiv, other)
 853
 854    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 855        return self._binop(IntDiv, other, reverse=True)
 856
 857    def __mod__(self, other: t.Any) -> Mod:
 858        return self._binop(Mod, other)
 859
 860    def __rmod__(self, other: t.Any) -> Mod:
 861        return self._binop(Mod, other, reverse=True)
 862
 863    def __pow__(self, other: t.Any) -> Pow:
 864        return self._binop(Pow, other)
 865
 866    def __rpow__(self, other: t.Any) -> Pow:
 867        return self._binop(Pow, other, reverse=True)
 868
 869    def __and__(self, other: t.Any) -> And:
 870        return self._binop(And, other)
 871
 872    def __rand__(self, other: t.Any) -> And:
 873        return self._binop(And, other, reverse=True)
 874
 875    def __or__(self, other: t.Any) -> Or:
 876        return self._binop(Or, other)
 877
 878    def __ror__(self, other: t.Any) -> Or:
 879        return self._binop(Or, other, reverse=True)
 880
 881    def __neg__(self) -> Neg:
 882        return Neg(this=_wrap(self.copy(), Binary))
 883
 884    def __invert__(self) -> Not:
 885        return not_(self.copy())
 886
 887
 888class Predicate(Condition):
 889    """Relationships like x = y, x > 1, x >= y."""
 890
 891
 892class DerivedTable(Expression):
 893    @property
 894    def selects(self) -> t.List[Expression]:
 895        return self.this.selects if isinstance(self.this, Subqueryable) else []
 896
 897    @property
 898    def named_selects(self) -> t.List[str]:
 899        return [select.output_name for select in self.selects]
 900
 901
 902class Unionable(Expression):
 903    def union(
 904        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 905    ) -> Unionable:
 906        """
 907        Builds a UNION expression.
 908
 909        Example:
 910            >>> import sqlglot
 911            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 912            'SELECT * FROM foo UNION SELECT * FROM bla'
 913
 914        Args:
 915            expression: the SQL code string.
 916                If an `Expression` instance is passed, it will be used as-is.
 917            distinct: set the DISTINCT flag if and only if this is true.
 918            dialect: the dialect used to parse the input expression.
 919            opts: other options to use to parse the input expressions.
 920
 921        Returns:
 922            The new Union expression.
 923        """
 924        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 925
 926    def intersect(
 927        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 928    ) -> Unionable:
 929        """
 930        Builds an INTERSECT expression.
 931
 932        Example:
 933            >>> import sqlglot
 934            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 935            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 936
 937        Args:
 938            expression: the SQL code string.
 939                If an `Expression` instance is passed, it will be used as-is.
 940            distinct: set the DISTINCT flag if and only if this is true.
 941            dialect: the dialect used to parse the input expression.
 942            opts: other options to use to parse the input expressions.
 943
 944        Returns:
 945            The new Intersect expression.
 946        """
 947        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 948
 949    def except_(
 950        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 951    ) -> Unionable:
 952        """
 953        Builds an EXCEPT expression.
 954
 955        Example:
 956            >>> import sqlglot
 957            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 958            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 959
 960        Args:
 961            expression: the SQL code string.
 962                If an `Expression` instance is passed, it will be used as-is.
 963            distinct: set the DISTINCT flag if and only if this is true.
 964            dialect: the dialect used to parse the input expression.
 965            opts: other options to use to parse the input expressions.
 966
 967        Returns:
 968            The new Except expression.
 969        """
 970        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 971
 972
 973class UDTF(DerivedTable, Unionable):
 974    @property
 975    def selects(self) -> t.List[Expression]:
 976        alias = self.args.get("alias")
 977        return alias.columns if alias else []
 978
 979
 980class Cache(Expression):
 981    arg_types = {
 982        "with": False,
 983        "this": True,
 984        "lazy": False,
 985        "options": False,
 986        "expression": False,
 987    }
 988
 989
 990class Uncache(Expression):
 991    arg_types = {"this": True, "exists": False}
 992
 993
 994class DDL(Expression):
 995    @property
 996    def ctes(self):
 997        with_ = self.args.get("with")
 998        if not with_:
 999            return []
1000        return with_.expressions
1001
1002    @property
1003    def named_selects(self) -> t.List[str]:
1004        if isinstance(self.expression, Subqueryable):
1005            return self.expression.named_selects
1006        return []
1007
1008    @property
1009    def selects(self) -> t.List[Expression]:
1010        if isinstance(self.expression, Subqueryable):
1011            return self.expression.selects
1012        return []
1013
1014
1015class Create(DDL):
1016    arg_types = {
1017        "with": False,
1018        "this": True,
1019        "kind": True,
1020        "expression": False,
1021        "exists": False,
1022        "properties": False,
1023        "replace": False,
1024        "unique": False,
1025        "indexes": False,
1026        "no_schema_binding": False,
1027        "begin": False,
1028        "clone": False,
1029    }
1030
1031
1032# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1033class Clone(Expression):
1034    arg_types = {
1035        "this": True,
1036        "when": False,
1037        "kind": False,
1038        "expression": False,
1039    }
1040
1041
1042class Describe(Expression):
1043    arg_types = {"this": True, "kind": False}
1044
1045
1046class Pragma(Expression):
1047    pass
1048
1049
1050class Set(Expression):
1051    arg_types = {"expressions": False, "unset": False, "tag": False}
1052
1053
1054class SetItem(Expression):
1055    arg_types = {
1056        "this": False,
1057        "expressions": False,
1058        "kind": False,
1059        "collate": False,  # MySQL SET NAMES statement
1060        "global": False,
1061    }
1062
1063
1064class Show(Expression):
1065    arg_types = {
1066        "this": True,
1067        "target": False,
1068        "offset": False,
1069        "limit": False,
1070        "like": False,
1071        "where": False,
1072        "db": False,
1073        "full": False,
1074        "mutex": False,
1075        "query": False,
1076        "channel": False,
1077        "global": False,
1078        "log": False,
1079        "position": False,
1080        "types": False,
1081    }
1082
1083
1084class UserDefinedFunction(Expression):
1085    arg_types = {"this": True, "expressions": False, "wrapped": False}
1086
1087
1088class CharacterSet(Expression):
1089    arg_types = {"this": True, "default": False}
1090
1091
1092class With(Expression):
1093    arg_types = {"expressions": True, "recursive": False}
1094
1095    @property
1096    def recursive(self) -> bool:
1097        return bool(self.args.get("recursive"))
1098
1099
1100class WithinGroup(Expression):
1101    arg_types = {"this": True, "expression": False}
1102
1103
1104class CTE(DerivedTable):
1105    arg_types = {"this": True, "alias": True}
1106
1107
1108class TableAlias(Expression):
1109    arg_types = {"this": False, "columns": False}
1110
1111    @property
1112    def columns(self):
1113        return self.args.get("columns") or []
1114
1115
1116class BitString(Condition):
1117    pass
1118
1119
1120class HexString(Condition):
1121    pass
1122
1123
1124class ByteString(Condition):
1125    pass
1126
1127
1128class RawString(Condition):
1129    pass
1130
1131
1132class Column(Condition):
1133    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1134
1135    @property
1136    def table(self) -> str:
1137        return self.text("table")
1138
1139    @property
1140    def db(self) -> str:
1141        return self.text("db")
1142
1143    @property
1144    def catalog(self) -> str:
1145        return self.text("catalog")
1146
1147    @property
1148    def output_name(self) -> str:
1149        return self.name
1150
1151    @property
1152    def parts(self) -> t.List[Identifier]:
1153        """Return the parts of a column in order catalog, db, table, name."""
1154        return [
1155            t.cast(Identifier, self.args[part])
1156            for part in ("catalog", "db", "table", "this")
1157            if self.args.get(part)
1158        ]
1159
1160    def to_dot(self) -> Dot:
1161        """Converts the column into a dot expression."""
1162        parts = self.parts
1163        parent = self.parent
1164
1165        while parent:
1166            if isinstance(parent, Dot):
1167                parts.append(parent.expression)
1168            parent = parent.parent
1169
1170        return Dot.build(parts)
1171
1172
1173class ColumnPosition(Expression):
1174    arg_types = {"this": False, "position": True}
1175
1176
1177class ColumnDef(Expression):
1178    arg_types = {
1179        "this": True,
1180        "kind": False,
1181        "constraints": False,
1182        "exists": False,
1183        "position": False,
1184    }
1185
1186    @property
1187    def constraints(self) -> t.List[ColumnConstraint]:
1188        return self.args.get("constraints") or []
1189
1190
1191class AlterColumn(Expression):
1192    arg_types = {
1193        "this": True,
1194        "dtype": False,
1195        "collate": False,
1196        "using": False,
1197        "default": False,
1198        "drop": False,
1199    }
1200
1201
1202class RenameTable(Expression):
1203    pass
1204
1205
1206class Comment(Expression):
1207    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1208
1209
1210# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1211class MergeTreeTTLAction(Expression):
1212    arg_types = {
1213        "this": True,
1214        "delete": False,
1215        "recompress": False,
1216        "to_disk": False,
1217        "to_volume": False,
1218    }
1219
1220
1221# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1222class MergeTreeTTL(Expression):
1223    arg_types = {
1224        "expressions": True,
1225        "where": False,
1226        "group": False,
1227        "aggregates": False,
1228    }
1229
1230
1231# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1232class IndexConstraintOption(Expression):
1233    arg_types = {
1234        "key_block_size": False,
1235        "using": False,
1236        "parser": False,
1237        "comment": False,
1238        "visible": False,
1239        "engine_attr": False,
1240        "secondary_engine_attr": False,
1241    }
1242
1243
1244class ColumnConstraint(Expression):
1245    arg_types = {"this": False, "kind": True}
1246
1247    @property
1248    def kind(self) -> ColumnConstraintKind:
1249        return self.args["kind"]
1250
1251
1252class ColumnConstraintKind(Expression):
1253    pass
1254
1255
1256class AutoIncrementColumnConstraint(ColumnConstraintKind):
1257    pass
1258
1259
1260class CaseSpecificColumnConstraint(ColumnConstraintKind):
1261    arg_types = {"not_": True}
1262
1263
1264class CharacterSetColumnConstraint(ColumnConstraintKind):
1265    arg_types = {"this": True}
1266
1267
1268class CheckColumnConstraint(ColumnConstraintKind):
1269    pass
1270
1271
1272class ClusteredColumnConstraint(ColumnConstraintKind):
1273    pass
1274
1275
1276class CollateColumnConstraint(ColumnConstraintKind):
1277    pass
1278
1279
1280class CommentColumnConstraint(ColumnConstraintKind):
1281    pass
1282
1283
1284class CompressColumnConstraint(ColumnConstraintKind):
1285    pass
1286
1287
1288class DateFormatColumnConstraint(ColumnConstraintKind):
1289    arg_types = {"this": True}
1290
1291
1292class DefaultColumnConstraint(ColumnConstraintKind):
1293    pass
1294
1295
1296class EncodeColumnConstraint(ColumnConstraintKind):
1297    pass
1298
1299
1300class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1301    # this: True -> ALWAYS, this: False -> BY DEFAULT
1302    arg_types = {
1303        "this": False,
1304        "expression": False,
1305        "on_null": False,
1306        "start": False,
1307        "increment": False,
1308        "minvalue": False,
1309        "maxvalue": False,
1310        "cycle": False,
1311    }
1312
1313
1314# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1315class IndexColumnConstraint(ColumnConstraintKind):
1316    arg_types = {"this": False, "schema": True, "kind": False, "type": False, "options": False}
1317
1318
1319class InlineLengthColumnConstraint(ColumnConstraintKind):
1320    pass
1321
1322
1323class NotForReplicationColumnConstraint(ColumnConstraintKind):
1324    arg_types = {}
1325
1326
1327class NotNullColumnConstraint(ColumnConstraintKind):
1328    arg_types = {"allow_null": False}
1329
1330
1331# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1332class OnUpdateColumnConstraint(ColumnConstraintKind):
1333    pass
1334
1335
1336class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1337    arg_types = {"desc": False}
1338
1339
1340class TitleColumnConstraint(ColumnConstraintKind):
1341    pass
1342
1343
1344class UniqueColumnConstraint(ColumnConstraintKind):
1345    arg_types = {"this": False}
1346
1347
1348class UppercaseColumnConstraint(ColumnConstraintKind):
1349    arg_types: t.Dict[str, t.Any] = {}
1350
1351
1352class PathColumnConstraint(ColumnConstraintKind):
1353    pass
1354
1355
1356# computed column expression
1357# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1358class ComputedColumnConstraint(ColumnConstraintKind):
1359    arg_types = {"this": True, "persisted": False, "not_null": False}
1360
1361
1362class Constraint(Expression):
1363    arg_types = {"this": True, "expressions": True}
1364
1365
1366class Delete(Expression):
1367    arg_types = {
1368        "with": False,
1369        "this": False,
1370        "using": False,
1371        "where": False,
1372        "returning": False,
1373        "limit": False,
1374        "tables": False,  # Multiple-Table Syntax (MySQL)
1375    }
1376
1377    def delete(
1378        self,
1379        table: ExpOrStr,
1380        dialect: DialectType = None,
1381        copy: bool = True,
1382        **opts,
1383    ) -> Delete:
1384        """
1385        Create a DELETE expression or replace the table on an existing DELETE expression.
1386
1387        Example:
1388            >>> delete("tbl").sql()
1389            'DELETE FROM tbl'
1390
1391        Args:
1392            table: the table from which to delete.
1393            dialect: the dialect used to parse the input expression.
1394            copy: if `False`, modify this expression instance in-place.
1395            opts: other options to use to parse the input expressions.
1396
1397        Returns:
1398            Delete: the modified expression.
1399        """
1400        return _apply_builder(
1401            expression=table,
1402            instance=self,
1403            arg="this",
1404            dialect=dialect,
1405            into=Table,
1406            copy=copy,
1407            **opts,
1408        )
1409
1410    def where(
1411        self,
1412        *expressions: t.Optional[ExpOrStr],
1413        append: bool = True,
1414        dialect: DialectType = None,
1415        copy: bool = True,
1416        **opts,
1417    ) -> Delete:
1418        """
1419        Append to or set the WHERE expressions.
1420
1421        Example:
1422            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1423            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1424
1425        Args:
1426            *expressions: the SQL code strings to parse.
1427                If an `Expression` instance is passed, it will be used as-is.
1428                Multiple expressions are combined with an AND operator.
1429            append: if `True`, AND the new expressions to any existing expression.
1430                Otherwise, this resets the expression.
1431            dialect: the dialect used to parse the input expressions.
1432            copy: if `False`, modify this expression instance in-place.
1433            opts: other options to use to parse the input expressions.
1434
1435        Returns:
1436            Delete: the modified expression.
1437        """
1438        return _apply_conjunction_builder(
1439            *expressions,
1440            instance=self,
1441            arg="where",
1442            append=append,
1443            into=Where,
1444            dialect=dialect,
1445            copy=copy,
1446            **opts,
1447        )
1448
1449    def returning(
1450        self,
1451        expression: ExpOrStr,
1452        dialect: DialectType = None,
1453        copy: bool = True,
1454        **opts,
1455    ) -> Delete:
1456        """
1457        Set the RETURNING expression. Not supported by all dialects.
1458
1459        Example:
1460            >>> delete("tbl").returning("*", dialect="postgres").sql()
1461            'DELETE FROM tbl RETURNING *'
1462
1463        Args:
1464            expression: the SQL code strings to parse.
1465                If an `Expression` instance is passed, it will be used as-is.
1466            dialect: the dialect used to parse the input expressions.
1467            copy: if `False`, modify this expression instance in-place.
1468            opts: other options to use to parse the input expressions.
1469
1470        Returns:
1471            Delete: the modified expression.
1472        """
1473        return _apply_builder(
1474            expression=expression,
1475            instance=self,
1476            arg="returning",
1477            prefix="RETURNING",
1478            dialect=dialect,
1479            copy=copy,
1480            into=Returning,
1481            **opts,
1482        )
1483
1484
1485class Drop(Expression):
1486    arg_types = {
1487        "this": False,
1488        "kind": False,
1489        "exists": False,
1490        "temporary": False,
1491        "materialized": False,
1492        "cascade": False,
1493        "constraints": False,
1494        "purge": False,
1495    }
1496
1497
1498class Filter(Expression):
1499    arg_types = {"this": True, "expression": True}
1500
1501
1502class Check(Expression):
1503    pass
1504
1505
1506class Directory(Expression):
1507    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1508    arg_types = {"this": True, "local": False, "row_format": False}
1509
1510
1511class ForeignKey(Expression):
1512    arg_types = {
1513        "expressions": True,
1514        "reference": False,
1515        "delete": False,
1516        "update": False,
1517    }
1518
1519
1520class PrimaryKey(Expression):
1521    arg_types = {"expressions": True, "options": False}
1522
1523
1524# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1525# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1526class Into(Expression):
1527    arg_types = {"this": True, "temporary": False, "unlogged": False}
1528
1529
1530class From(Expression):
1531    @property
1532    def name(self) -> str:
1533        return self.this.name
1534
1535    @property
1536    def alias_or_name(self) -> str:
1537        return self.this.alias_or_name
1538
1539
1540class Having(Expression):
1541    pass
1542
1543
1544class Hint(Expression):
1545    arg_types = {"expressions": True}
1546
1547
1548class JoinHint(Expression):
1549    arg_types = {"this": True, "expressions": True}
1550
1551
1552class Identifier(Expression):
1553    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1554
1555    @property
1556    def quoted(self) -> bool:
1557        return bool(self.args.get("quoted"))
1558
1559    @property
1560    def hashable_args(self) -> t.Any:
1561        return (self.this, self.quoted)
1562
1563    @property
1564    def output_name(self) -> str:
1565        return self.name
1566
1567
1568class Index(Expression):
1569    arg_types = {
1570        "this": False,
1571        "table": False,
1572        "using": False,
1573        "where": False,
1574        "columns": False,
1575        "unique": False,
1576        "primary": False,
1577        "amp": False,  # teradata
1578        "partition_by": False,  # teradata
1579    }
1580
1581
1582class Insert(DDL):
1583    arg_types = {
1584        "with": False,
1585        "this": True,
1586        "expression": False,
1587        "conflict": False,
1588        "returning": False,
1589        "overwrite": False,
1590        "exists": False,
1591        "partition": False,
1592        "alternative": False,
1593        "where": False,
1594        "ignore": False,
1595    }
1596
1597    def with_(
1598        self,
1599        alias: ExpOrStr,
1600        as_: ExpOrStr,
1601        recursive: t.Optional[bool] = None,
1602        append: bool = True,
1603        dialect: DialectType = None,
1604        copy: bool = True,
1605        **opts,
1606    ) -> Insert:
1607        """
1608        Append to or set the common table expressions.
1609
1610        Example:
1611            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1612            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1613
1614        Args:
1615            alias: the SQL code string to parse as the table name.
1616                If an `Expression` instance is passed, this is used as-is.
1617            as_: the SQL code string to parse as the table expression.
1618                If an `Expression` instance is passed, it will be used as-is.
1619            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1620            append: if `True`, add to any existing expressions.
1621                Otherwise, this resets the expressions.
1622            dialect: the dialect used to parse the input expression.
1623            copy: if `False`, modify this expression instance in-place.
1624            opts: other options to use to parse the input expressions.
1625
1626        Returns:
1627            The modified expression.
1628        """
1629        return _apply_cte_builder(
1630            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1631        )
1632
1633
1634class OnConflict(Expression):
1635    arg_types = {
1636        "duplicate": False,
1637        "expressions": False,
1638        "nothing": False,
1639        "key": False,
1640        "constraint": False,
1641    }
1642
1643
1644class Returning(Expression):
1645    arg_types = {"expressions": True, "into": False}
1646
1647
1648# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1649class Introducer(Expression):
1650    arg_types = {"this": True, "expression": True}
1651
1652
1653# national char, like n'utf8'
1654class National(Expression):
1655    pass
1656
1657
1658class LoadData(Expression):
1659    arg_types = {
1660        "this": True,
1661        "local": False,
1662        "overwrite": False,
1663        "inpath": True,
1664        "partition": False,
1665        "input_format": False,
1666        "serde": False,
1667    }
1668
1669
1670class Partition(Expression):
1671    arg_types = {"expressions": True}
1672
1673
1674class Fetch(Expression):
1675    arg_types = {
1676        "direction": False,
1677        "count": False,
1678        "percent": False,
1679        "with_ties": False,
1680    }
1681
1682
1683class Group(Expression):
1684    arg_types = {
1685        "expressions": False,
1686        "grouping_sets": False,
1687        "cube": False,
1688        "rollup": False,
1689        "totals": False,
1690        "all": False,
1691    }
1692
1693
1694class Lambda(Expression):
1695    arg_types = {"this": True, "expressions": True}
1696
1697
1698class Limit(Expression):
1699    arg_types = {"this": False, "expression": True, "offset": False}
1700
1701
1702class Literal(Condition):
1703    arg_types = {"this": True, "is_string": True}
1704
1705    @property
1706    def hashable_args(self) -> t.Any:
1707        return (self.this, self.args.get("is_string"))
1708
1709    @classmethod
1710    def number(cls, number) -> Literal:
1711        return cls(this=str(number), is_string=False)
1712
1713    @classmethod
1714    def string(cls, string) -> Literal:
1715        return cls(this=str(string), is_string=True)
1716
1717    @property
1718    def output_name(self) -> str:
1719        return self.name
1720
1721
1722class Join(Expression):
1723    arg_types = {
1724        "this": True,
1725        "on": False,
1726        "side": False,
1727        "kind": False,
1728        "using": False,
1729        "method": False,
1730        "global": False,
1731        "hint": False,
1732    }
1733
1734    @property
1735    def method(self) -> str:
1736        return self.text("method").upper()
1737
1738    @property
1739    def kind(self) -> str:
1740        return self.text("kind").upper()
1741
1742    @property
1743    def side(self) -> str:
1744        return self.text("side").upper()
1745
1746    @property
1747    def hint(self) -> str:
1748        return self.text("hint").upper()
1749
1750    @property
1751    def alias_or_name(self) -> str:
1752        return self.this.alias_or_name
1753
1754    def on(
1755        self,
1756        *expressions: t.Optional[ExpOrStr],
1757        append: bool = True,
1758        dialect: DialectType = None,
1759        copy: bool = True,
1760        **opts,
1761    ) -> Join:
1762        """
1763        Append to or set the ON expressions.
1764
1765        Example:
1766            >>> import sqlglot
1767            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1768            'JOIN x ON y = 1'
1769
1770        Args:
1771            *expressions: the SQL code strings to parse.
1772                If an `Expression` instance is passed, it will be used as-is.
1773                Multiple expressions are combined with an AND operator.
1774            append: if `True`, AND the new expressions to any existing expression.
1775                Otherwise, this resets the expression.
1776            dialect: the dialect used to parse the input expressions.
1777            copy: if `False`, modify this expression instance in-place.
1778            opts: other options to use to parse the input expressions.
1779
1780        Returns:
1781            The modified Join expression.
1782        """
1783        join = _apply_conjunction_builder(
1784            *expressions,
1785            instance=self,
1786            arg="on",
1787            append=append,
1788            dialect=dialect,
1789            copy=copy,
1790            **opts,
1791        )
1792
1793        if join.kind == "CROSS":
1794            join.set("kind", None)
1795
1796        return join
1797
1798    def using(
1799        self,
1800        *expressions: t.Optional[ExpOrStr],
1801        append: bool = True,
1802        dialect: DialectType = None,
1803        copy: bool = True,
1804        **opts,
1805    ) -> Join:
1806        """
1807        Append to or set the USING expressions.
1808
1809        Example:
1810            >>> import sqlglot
1811            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1812            'JOIN x USING (foo, bla)'
1813
1814        Args:
1815            *expressions: the SQL code strings to parse.
1816                If an `Expression` instance is passed, it will be used as-is.
1817            append: if `True`, concatenate the new expressions to the existing "using" list.
1818                Otherwise, this resets the expression.
1819            dialect: the dialect used to parse the input expressions.
1820            copy: if `False`, modify this expression instance in-place.
1821            opts: other options to use to parse the input expressions.
1822
1823        Returns:
1824            The modified Join expression.
1825        """
1826        join = _apply_list_builder(
1827            *expressions,
1828            instance=self,
1829            arg="using",
1830            append=append,
1831            dialect=dialect,
1832            copy=copy,
1833            **opts,
1834        )
1835
1836        if join.kind == "CROSS":
1837            join.set("kind", None)
1838
1839        return join
1840
1841
1842class Lateral(UDTF):
1843    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1844
1845
1846class MatchRecognize(Expression):
1847    arg_types = {
1848        "partition_by": False,
1849        "order": False,
1850        "measures": False,
1851        "rows": False,
1852        "after": False,
1853        "pattern": False,
1854        "define": False,
1855        "alias": False,
1856    }
1857
1858
1859# Clickhouse FROM FINAL modifier
1860# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1861class Final(Expression):
1862    pass
1863
1864
1865class Offset(Expression):
1866    arg_types = {"this": False, "expression": True}
1867
1868
1869class Order(Expression):
1870    arg_types = {"this": False, "expressions": True}
1871
1872
1873# hive specific sorts
1874# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1875class Cluster(Order):
1876    pass
1877
1878
1879class Distribute(Order):
1880    pass
1881
1882
1883class Sort(Order):
1884    pass
1885
1886
1887class Ordered(Expression):
1888    arg_types = {"this": True, "desc": True, "nulls_first": True}
1889
1890
1891class Property(Expression):
1892    arg_types = {"this": True, "value": True}
1893
1894
1895class AlgorithmProperty(Property):
1896    arg_types = {"this": True}
1897
1898
1899class AutoIncrementProperty(Property):
1900    arg_types = {"this": True}
1901
1902
1903class BlockCompressionProperty(Property):
1904    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1905
1906
1907class CharacterSetProperty(Property):
1908    arg_types = {"this": True, "default": True}
1909
1910
1911class ChecksumProperty(Property):
1912    arg_types = {"on": False, "default": False}
1913
1914
1915class CollateProperty(Property):
1916    arg_types = {"this": True}
1917
1918
1919class CopyGrantsProperty(Property):
1920    arg_types = {}
1921
1922
1923class DataBlocksizeProperty(Property):
1924    arg_types = {
1925        "size": False,
1926        "units": False,
1927        "minimum": False,
1928        "maximum": False,
1929        "default": False,
1930    }
1931
1932
1933class DefinerProperty(Property):
1934    arg_types = {"this": True}
1935
1936
1937class DistKeyProperty(Property):
1938    arg_types = {"this": True}
1939
1940
1941class DistStyleProperty(Property):
1942    arg_types = {"this": True}
1943
1944
1945class EngineProperty(Property):
1946    arg_types = {"this": True}
1947
1948
1949class HeapProperty(Property):
1950    arg_types = {}
1951
1952
1953class ToTableProperty(Property):
1954    arg_types = {"this": True}
1955
1956
1957class ExecuteAsProperty(Property):
1958    arg_types = {"this": True}
1959
1960
1961class ExternalProperty(Property):
1962    arg_types = {"this": False}
1963
1964
1965class FallbackProperty(Property):
1966    arg_types = {"no": True, "protection": False}
1967
1968
1969class FileFormatProperty(Property):
1970    arg_types = {"this": True}
1971
1972
1973class FreespaceProperty(Property):
1974    arg_types = {"this": True, "percent": False}
1975
1976
1977class InputOutputFormat(Expression):
1978    arg_types = {"input_format": False, "output_format": False}
1979
1980
1981class IsolatedLoadingProperty(Property):
1982    arg_types = {
1983        "no": True,
1984        "concurrent": True,
1985        "for_all": True,
1986        "for_insert": True,
1987        "for_none": True,
1988    }
1989
1990
1991class JournalProperty(Property):
1992    arg_types = {
1993        "no": False,
1994        "dual": False,
1995        "before": False,
1996        "local": False,
1997        "after": False,
1998    }
1999
2000
2001class LanguageProperty(Property):
2002    arg_types = {"this": True}
2003
2004
2005# spark ddl
2006class ClusteredByProperty(Property):
2007    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2008
2009
2010class DictProperty(Property):
2011    arg_types = {"this": True, "kind": True, "settings": False}
2012
2013
2014class DictSubProperty(Property):
2015    pass
2016
2017
2018class DictRange(Property):
2019    arg_types = {"this": True, "min": True, "max": True}
2020
2021
2022# Clickhouse CREATE ... ON CLUSTER modifier
2023# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2024class OnCluster(Property):
2025    arg_types = {"this": True}
2026
2027
2028class LikeProperty(Property):
2029    arg_types = {"this": True, "expressions": False}
2030
2031
2032class LocationProperty(Property):
2033    arg_types = {"this": True}
2034
2035
2036class LockingProperty(Property):
2037    arg_types = {
2038        "this": False,
2039        "kind": True,
2040        "for_or_in": True,
2041        "lock_type": True,
2042        "override": False,
2043    }
2044
2045
2046class LogProperty(Property):
2047    arg_types = {"no": True}
2048
2049
2050class MaterializedProperty(Property):
2051    arg_types = {"this": False}
2052
2053
2054class MergeBlockRatioProperty(Property):
2055    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2056
2057
2058class NoPrimaryIndexProperty(Property):
2059    arg_types = {}
2060
2061
2062class OnProperty(Property):
2063    arg_types = {"this": True}
2064
2065
2066class OnCommitProperty(Property):
2067    arg_types = {"delete": False}
2068
2069
2070class PartitionedByProperty(Property):
2071    arg_types = {"this": True}
2072
2073
2074class ReturnsProperty(Property):
2075    arg_types = {"this": True, "is_table": False, "table": False}
2076
2077
2078class RowFormatProperty(Property):
2079    arg_types = {"this": True}
2080
2081
2082class RowFormatDelimitedProperty(Property):
2083    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2084    arg_types = {
2085        "fields": False,
2086        "escaped": False,
2087        "collection_items": False,
2088        "map_keys": False,
2089        "lines": False,
2090        "null": False,
2091        "serde": False,
2092    }
2093
2094
2095class RowFormatSerdeProperty(Property):
2096    arg_types = {"this": True, "serde_properties": False}
2097
2098
2099# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2100class QueryTransform(Expression):
2101    arg_types = {
2102        "expressions": True,
2103        "command_script": True,
2104        "schema": False,
2105        "row_format_before": False,
2106        "record_writer": False,
2107        "row_format_after": False,
2108        "record_reader": False,
2109    }
2110
2111
2112class SchemaCommentProperty(Property):
2113    arg_types = {"this": True}
2114
2115
2116class SerdeProperties(Property):
2117    arg_types = {"expressions": True}
2118
2119
2120class SetProperty(Property):
2121    arg_types = {"multi": True}
2122
2123
2124class SettingsProperty(Property):
2125    arg_types = {"expressions": True}
2126
2127
2128class SortKeyProperty(Property):
2129    arg_types = {"this": True, "compound": False}
2130
2131
2132class SqlSecurityProperty(Property):
2133    arg_types = {"definer": True}
2134
2135
2136class StabilityProperty(Property):
2137    arg_types = {"this": True}
2138
2139
2140class TemporaryProperty(Property):
2141    arg_types = {}
2142
2143
2144class TransientProperty(Property):
2145    arg_types = {"this": False}
2146
2147
2148class VolatileProperty(Property):
2149    arg_types = {"this": False}
2150
2151
2152class WithDataProperty(Property):
2153    arg_types = {"no": True, "statistics": False}
2154
2155
2156class WithJournalTableProperty(Property):
2157    arg_types = {"this": True}
2158
2159
2160class Properties(Expression):
2161    arg_types = {"expressions": True}
2162
2163    NAME_TO_PROPERTY = {
2164        "ALGORITHM": AlgorithmProperty,
2165        "AUTO_INCREMENT": AutoIncrementProperty,
2166        "CHARACTER SET": CharacterSetProperty,
2167        "CLUSTERED_BY": ClusteredByProperty,
2168        "COLLATE": CollateProperty,
2169        "COMMENT": SchemaCommentProperty,
2170        "DEFINER": DefinerProperty,
2171        "DISTKEY": DistKeyProperty,
2172        "DISTSTYLE": DistStyleProperty,
2173        "ENGINE": EngineProperty,
2174        "EXECUTE AS": ExecuteAsProperty,
2175        "FORMAT": FileFormatProperty,
2176        "LANGUAGE": LanguageProperty,
2177        "LOCATION": LocationProperty,
2178        "PARTITIONED_BY": PartitionedByProperty,
2179        "RETURNS": ReturnsProperty,
2180        "ROW_FORMAT": RowFormatProperty,
2181        "SORTKEY": SortKeyProperty,
2182    }
2183
2184    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2185
2186    # CREATE property locations
2187    # Form: schema specified
2188    #   create [POST_CREATE]
2189    #     table a [POST_NAME]
2190    #     (b int) [POST_SCHEMA]
2191    #     with ([POST_WITH])
2192    #     index (b) [POST_INDEX]
2193    #
2194    # Form: alias selection
2195    #   create [POST_CREATE]
2196    #     table a [POST_NAME]
2197    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2198    #     index (c) [POST_INDEX]
2199    class Location(AutoName):
2200        POST_CREATE = auto()
2201        POST_NAME = auto()
2202        POST_SCHEMA = auto()
2203        POST_WITH = auto()
2204        POST_ALIAS = auto()
2205        POST_EXPRESSION = auto()
2206        POST_INDEX = auto()
2207        UNSUPPORTED = auto()
2208
2209    @classmethod
2210    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2211        expressions = []
2212        for key, value in properties_dict.items():
2213            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2214            if property_cls:
2215                expressions.append(property_cls(this=convert(value)))
2216            else:
2217                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2218
2219        return cls(expressions=expressions)
2220
2221
2222class Qualify(Expression):
2223    pass
2224
2225
2226# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2227class Return(Expression):
2228    pass
2229
2230
2231class Reference(Expression):
2232    arg_types = {"this": True, "expressions": False, "options": False}
2233
2234
2235class Tuple(Expression):
2236    arg_types = {"expressions": False}
2237
2238    def isin(
2239        self,
2240        *expressions: t.Any,
2241        query: t.Optional[ExpOrStr] = None,
2242        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2243        copy: bool = True,
2244        **opts,
2245    ) -> In:
2246        return In(
2247            this=maybe_copy(self, copy),
2248            expressions=[convert(e, copy=copy) for e in expressions],
2249            query=maybe_parse(query, copy=copy, **opts) if query else None,
2250            unnest=Unnest(
2251                expressions=[
2252                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2253                ]
2254            )
2255            if unnest
2256            else None,
2257        )
2258
2259
2260class Subqueryable(Unionable):
2261    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2262        """
2263        Convert this expression to an aliased expression that can be used as a Subquery.
2264
2265        Example:
2266            >>> subquery = Select().select("x").from_("tbl").subquery()
2267            >>> Select().select("x").from_(subquery).sql()
2268            'SELECT x FROM (SELECT x FROM tbl)'
2269
2270        Args:
2271            alias (str | Identifier): an optional alias for the subquery
2272            copy (bool): if `False`, modify this expression instance in-place.
2273
2274        Returns:
2275            Alias: the subquery
2276        """
2277        instance = maybe_copy(self, copy)
2278        if not isinstance(alias, Expression):
2279            alias = TableAlias(this=to_identifier(alias)) if alias else None
2280
2281        return Subquery(this=instance, alias=alias)
2282
2283    def limit(
2284        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2285    ) -> Select:
2286        raise NotImplementedError
2287
2288    @property
2289    def ctes(self):
2290        with_ = self.args.get("with")
2291        if not with_:
2292            return []
2293        return with_.expressions
2294
2295    @property
2296    def selects(self) -> t.List[Expression]:
2297        raise NotImplementedError("Subqueryable objects must implement `selects`")
2298
2299    @property
2300    def named_selects(self) -> t.List[str]:
2301        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2302
2303    def select(
2304        self,
2305        *expressions: t.Optional[ExpOrStr],
2306        append: bool = True,
2307        dialect: DialectType = None,
2308        copy: bool = True,
2309        **opts,
2310    ) -> Subqueryable:
2311        raise NotImplementedError("Subqueryable objects must implement `select`")
2312
2313    def with_(
2314        self,
2315        alias: ExpOrStr,
2316        as_: ExpOrStr,
2317        recursive: t.Optional[bool] = None,
2318        append: bool = True,
2319        dialect: DialectType = None,
2320        copy: bool = True,
2321        **opts,
2322    ) -> Subqueryable:
2323        """
2324        Append to or set the common table expressions.
2325
2326        Example:
2327            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2328            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2329
2330        Args:
2331            alias: the SQL code string to parse as the table name.
2332                If an `Expression` instance is passed, this is used as-is.
2333            as_: the SQL code string to parse as the table expression.
2334                If an `Expression` instance is passed, it will be used as-is.
2335            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2336            append: if `True`, add to any existing expressions.
2337                Otherwise, this resets the expressions.
2338            dialect: the dialect used to parse the input expression.
2339            copy: if `False`, modify this expression instance in-place.
2340            opts: other options to use to parse the input expressions.
2341
2342        Returns:
2343            The modified expression.
2344        """
2345        return _apply_cte_builder(
2346            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2347        )
2348
2349
2350QUERY_MODIFIERS = {
2351    "match": False,
2352    "laterals": False,
2353    "joins": False,
2354    "pivots": False,
2355    "where": False,
2356    "group": False,
2357    "having": False,
2358    "qualify": False,
2359    "windows": False,
2360    "distribute": False,
2361    "sort": False,
2362    "cluster": False,
2363    "order": False,
2364    "limit": False,
2365    "offset": False,
2366    "locks": False,
2367    "sample": False,
2368    "settings": False,
2369    "format": False,
2370}
2371
2372
2373# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2374class WithTableHint(Expression):
2375    arg_types = {"expressions": True}
2376
2377
2378# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2379class IndexTableHint(Expression):
2380    arg_types = {"this": True, "expressions": False, "target": False}
2381
2382
2383class Table(Expression):
2384    arg_types = {
2385        "this": True,
2386        "alias": False,
2387        "db": False,
2388        "catalog": False,
2389        "laterals": False,
2390        "joins": False,
2391        "pivots": False,
2392        "hints": False,
2393        "system_time": False,
2394    }
2395
2396    @property
2397    def name(self) -> str:
2398        if isinstance(self.this, Func):
2399            return ""
2400        return self.this.name
2401
2402    @property
2403    def db(self) -> str:
2404        return self.text("db")
2405
2406    @property
2407    def catalog(self) -> str:
2408        return self.text("catalog")
2409
2410    @property
2411    def selects(self) -> t.List[Expression]:
2412        return []
2413
2414    @property
2415    def named_selects(self) -> t.List[str]:
2416        return []
2417
2418    @property
2419    def parts(self) -> t.List[Identifier]:
2420        """Return the parts of a table in order catalog, db, table."""
2421        parts: t.List[Identifier] = []
2422
2423        for arg in ("catalog", "db", "this"):
2424            part = self.args.get(arg)
2425
2426            if isinstance(part, Identifier):
2427                parts.append(part)
2428            elif isinstance(part, Dot):
2429                parts.extend(part.flatten())
2430
2431        return parts
2432
2433
2434# See the TSQL "Querying data in a system-versioned temporal table" page
2435class SystemTime(Expression):
2436    arg_types = {
2437        "this": False,
2438        "expression": False,
2439        "kind": True,
2440    }
2441
2442
2443class Union(Subqueryable):
2444    arg_types = {
2445        "with": False,
2446        "this": True,
2447        "expression": True,
2448        "distinct": False,
2449        **QUERY_MODIFIERS,
2450    }
2451
2452    def limit(
2453        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2454    ) -> Select:
2455        """
2456        Set the LIMIT expression.
2457
2458        Example:
2459            >>> select("1").union(select("1")).limit(1).sql()
2460            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2461
2462        Args:
2463            expression: the SQL code string to parse.
2464                This can also be an integer.
2465                If a `Limit` instance is passed, this is used as-is.
2466                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2467            dialect: the dialect used to parse the input expression.
2468            copy: if `False`, modify this expression instance in-place.
2469            opts: other options to use to parse the input expressions.
2470
2471        Returns:
2472            The limited subqueryable.
2473        """
2474        return (
2475            select("*")
2476            .from_(self.subquery(alias="_l_0", copy=copy))
2477            .limit(expression, dialect=dialect, copy=False, **opts)
2478        )
2479
2480    def select(
2481        self,
2482        *expressions: t.Optional[ExpOrStr],
2483        append: bool = True,
2484        dialect: DialectType = None,
2485        copy: bool = True,
2486        **opts,
2487    ) -> Union:
2488        """Append to or set the SELECT of the union recursively.
2489
2490        Example:
2491            >>> from sqlglot import parse_one
2492            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2493            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2494
2495        Args:
2496            *expressions: the SQL code strings to parse.
2497                If an `Expression` instance is passed, it will be used as-is.
2498            append: if `True`, add to any existing expressions.
2499                Otherwise, this resets the expressions.
2500            dialect: the dialect used to parse the input expressions.
2501            copy: if `False`, modify this expression instance in-place.
2502            opts: other options to use to parse the input expressions.
2503
2504        Returns:
2505            Union: the modified expression.
2506        """
2507        this = self.copy() if copy else self
2508        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2509        this.expression.unnest().select(
2510            *expressions, append=append, dialect=dialect, copy=False, **opts
2511        )
2512        return this
2513
2514    @property
2515    def named_selects(self) -> t.List[str]:
2516        return self.this.unnest().named_selects
2517
2518    @property
2519    def is_star(self) -> bool:
2520        return self.this.is_star or self.expression.is_star
2521
2522    @property
2523    def selects(self) -> t.List[Expression]:
2524        return self.this.unnest().selects
2525
2526    @property
2527    def left(self):
2528        return self.this
2529
2530    @property
2531    def right(self):
2532        return self.expression
2533
2534
2535class Except(Union):
2536    pass
2537
2538
2539class Intersect(Union):
2540    pass
2541
2542
2543class Unnest(UDTF):
2544    arg_types = {
2545        "expressions": True,
2546        "ordinality": False,
2547        "alias": False,
2548        "offset": False,
2549    }
2550
2551
2552class Update(Expression):
2553    arg_types = {
2554        "with": False,
2555        "this": False,
2556        "expressions": True,
2557        "from": False,
2558        "where": False,
2559        "returning": False,
2560        "limit": False,
2561    }
2562
2563
2564class Values(UDTF):
2565    arg_types = {
2566        "expressions": True,
2567        "ordinality": False,
2568        "alias": False,
2569    }
2570
2571
2572class Var(Expression):
2573    pass
2574
2575
2576class Schema(Expression):
2577    arg_types = {"this": False, "expressions": False}
2578
2579
2580# https://dev.mysql.com/doc/refman/8.0/en/select.html
2581# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2582class Lock(Expression):
2583    arg_types = {"update": True, "expressions": False, "wait": False}
2584
2585
2586class Select(Subqueryable):
2587    arg_types = {
2588        "with": False,
2589        "kind": False,
2590        "expressions": False,
2591        "hint": False,
2592        "distinct": False,
2593        "into": False,
2594        "from": False,
2595        **QUERY_MODIFIERS,
2596    }
2597
2598    def from_(
2599        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2600    ) -> Select:
2601        """
2602        Set the FROM expression.
2603
2604        Example:
2605            >>> Select().from_("tbl").select("x").sql()
2606            'SELECT x FROM tbl'
2607
2608        Args:
2609            expression : the SQL code strings to parse.
2610                If a `From` instance is passed, this is used as-is.
2611                If another `Expression` instance is passed, it will be wrapped in a `From`.
2612            dialect: the dialect used to parse the input expression.
2613            copy: if `False`, modify this expression instance in-place.
2614            opts: other options to use to parse the input expressions.
2615
2616        Returns:
2617            The modified Select expression.
2618        """
2619        return _apply_builder(
2620            expression=expression,
2621            instance=self,
2622            arg="from",
2623            into=From,
2624            prefix="FROM",
2625            dialect=dialect,
2626            copy=copy,
2627            **opts,
2628        )
2629
2630    def group_by(
2631        self,
2632        *expressions: t.Optional[ExpOrStr],
2633        append: bool = True,
2634        dialect: DialectType = None,
2635        copy: bool = True,
2636        **opts,
2637    ) -> Select:
2638        """
2639        Set the GROUP BY expression.
2640
2641        Example:
2642            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2643            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2644
2645        Args:
2646            *expressions: the SQL code strings to parse.
2647                If a `Group` instance is passed, this is used as-is.
2648                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2649                If nothing is passed in then a group by is not applied to the expression
2650            append: if `True`, add to any existing expressions.
2651                Otherwise, this flattens all the `Group` expression into a single expression.
2652            dialect: the dialect used to parse the input expression.
2653            copy: if `False`, modify this expression instance in-place.
2654            opts: other options to use to parse the input expressions.
2655
2656        Returns:
2657            The modified Select expression.
2658        """
2659        if not expressions:
2660            return self if not copy else self.copy()
2661
2662        return _apply_child_list_builder(
2663            *expressions,
2664            instance=self,
2665            arg="group",
2666            append=append,
2667            copy=copy,
2668            prefix="GROUP BY",
2669            into=Group,
2670            dialect=dialect,
2671            **opts,
2672        )
2673
2674    def order_by(
2675        self,
2676        *expressions: t.Optional[ExpOrStr],
2677        append: bool = True,
2678        dialect: DialectType = None,
2679        copy: bool = True,
2680        **opts,
2681    ) -> Select:
2682        """
2683        Set the ORDER BY expression.
2684
2685        Example:
2686            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2687            'SELECT x FROM tbl ORDER BY x DESC'
2688
2689        Args:
2690            *expressions: the SQL code strings to parse.
2691                If a `Group` instance is passed, this is used as-is.
2692                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2693            append: if `True`, add to any existing expressions.
2694                Otherwise, this flattens all the `Order` expression into a single expression.
2695            dialect: the dialect used to parse the input expression.
2696            copy: if `False`, modify this expression instance in-place.
2697            opts: other options to use to parse the input expressions.
2698
2699        Returns:
2700            The modified Select expression.
2701        """
2702        return _apply_child_list_builder(
2703            *expressions,
2704            instance=self,
2705            arg="order",
2706            append=append,
2707            copy=copy,
2708            prefix="ORDER BY",
2709            into=Order,
2710            dialect=dialect,
2711            **opts,
2712        )
2713
2714    def sort_by(
2715        self,
2716        *expressions: t.Optional[ExpOrStr],
2717        append: bool = True,
2718        dialect: DialectType = None,
2719        copy: bool = True,
2720        **opts,
2721    ) -> Select:
2722        """
2723        Set the SORT BY expression.
2724
2725        Example:
2726            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2727            'SELECT x FROM tbl SORT BY x DESC'
2728
2729        Args:
2730            *expressions: the SQL code strings to parse.
2731                If a `Group` instance is passed, this is used as-is.
2732                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2733            append: if `True`, add to any existing expressions.
2734                Otherwise, this flattens all the `Order` expression into a single expression.
2735            dialect: the dialect used to parse the input expression.
2736            copy: if `False`, modify this expression instance in-place.
2737            opts: other options to use to parse the input expressions.
2738
2739        Returns:
2740            The modified Select expression.
2741        """
2742        return _apply_child_list_builder(
2743            *expressions,
2744            instance=self,
2745            arg="sort",
2746            append=append,
2747            copy=copy,
2748            prefix="SORT BY",
2749            into=Sort,
2750            dialect=dialect,
2751            **opts,
2752        )
2753
2754    def cluster_by(
2755        self,
2756        *expressions: t.Optional[ExpOrStr],
2757        append: bool = True,
2758        dialect: DialectType = None,
2759        copy: bool = True,
2760        **opts,
2761    ) -> Select:
2762        """
2763        Set the CLUSTER BY expression.
2764
2765        Example:
2766            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2767            'SELECT x FROM tbl CLUSTER BY x DESC'
2768
2769        Args:
2770            *expressions: the SQL code strings to parse.
2771                If a `Group` instance is passed, this is used as-is.
2772                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2773            append: if `True`, add to any existing expressions.
2774                Otherwise, this flattens all the `Order` expression into a single expression.
2775            dialect: the dialect used to parse the input expression.
2776            copy: if `False`, modify this expression instance in-place.
2777            opts: other options to use to parse the input expressions.
2778
2779        Returns:
2780            The modified Select expression.
2781        """
2782        return _apply_child_list_builder(
2783            *expressions,
2784            instance=self,
2785            arg="cluster",
2786            append=append,
2787            copy=copy,
2788            prefix="CLUSTER BY",
2789            into=Cluster,
2790            dialect=dialect,
2791            **opts,
2792        )
2793
2794    def limit(
2795        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2796    ) -> Select:
2797        """
2798        Set the LIMIT expression.
2799
2800        Example:
2801            >>> Select().from_("tbl").select("x").limit(10).sql()
2802            'SELECT x FROM tbl LIMIT 10'
2803
2804        Args:
2805            expression: the SQL code string to parse.
2806                This can also be an integer.
2807                If a `Limit` instance is passed, this is used as-is.
2808                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2809            dialect: the dialect used to parse the input expression.
2810            copy: if `False`, modify this expression instance in-place.
2811            opts: other options to use to parse the input expressions.
2812
2813        Returns:
2814            Select: the modified expression.
2815        """
2816        return _apply_builder(
2817            expression=expression,
2818            instance=self,
2819            arg="limit",
2820            into=Limit,
2821            prefix="LIMIT",
2822            dialect=dialect,
2823            copy=copy,
2824            **opts,
2825        )
2826
2827    def offset(
2828        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2829    ) -> Select:
2830        """
2831        Set the OFFSET expression.
2832
2833        Example:
2834            >>> Select().from_("tbl").select("x").offset(10).sql()
2835            'SELECT x FROM tbl OFFSET 10'
2836
2837        Args:
2838            expression: the SQL code string to parse.
2839                This can also be an integer.
2840                If a `Offset` instance is passed, this is used as-is.
2841                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2842            dialect: the dialect used to parse the input expression.
2843            copy: if `False`, modify this expression instance in-place.
2844            opts: other options to use to parse the input expressions.
2845
2846        Returns:
2847            The modified Select expression.
2848        """
2849        return _apply_builder(
2850            expression=expression,
2851            instance=self,
2852            arg="offset",
2853            into=Offset,
2854            prefix="OFFSET",
2855            dialect=dialect,
2856            copy=copy,
2857            **opts,
2858        )
2859
2860    def select(
2861        self,
2862        *expressions: t.Optional[ExpOrStr],
2863        append: bool = True,
2864        dialect: DialectType = None,
2865        copy: bool = True,
2866        **opts,
2867    ) -> Select:
2868        """
2869        Append to or set the SELECT expressions.
2870
2871        Example:
2872            >>> Select().select("x", "y").sql()
2873            'SELECT x, y'
2874
2875        Args:
2876            *expressions: the SQL code strings to parse.
2877                If an `Expression` instance is passed, it will be used as-is.
2878            append: if `True`, add to any existing expressions.
2879                Otherwise, this resets the expressions.
2880            dialect: the dialect used to parse the input expressions.
2881            copy: if `False`, modify this expression instance in-place.
2882            opts: other options to use to parse the input expressions.
2883
2884        Returns:
2885            The modified Select expression.
2886        """
2887        return _apply_list_builder(
2888            *expressions,
2889            instance=self,
2890            arg="expressions",
2891            append=append,
2892            dialect=dialect,
2893            copy=copy,
2894            **opts,
2895        )
2896
2897    def lateral(
2898        self,
2899        *expressions: t.Optional[ExpOrStr],
2900        append: bool = True,
2901        dialect: DialectType = None,
2902        copy: bool = True,
2903        **opts,
2904    ) -> Select:
2905        """
2906        Append to or set the LATERAL expressions.
2907
2908        Example:
2909            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2910            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2911
2912        Args:
2913            *expressions: the SQL code strings to parse.
2914                If an `Expression` instance is passed, it will be used as-is.
2915            append: if `True`, add to any existing expressions.
2916                Otherwise, this resets the expressions.
2917            dialect: the dialect used to parse the input expressions.
2918            copy: if `False`, modify this expression instance in-place.
2919            opts: other options to use to parse the input expressions.
2920
2921        Returns:
2922            The modified Select expression.
2923        """
2924        return _apply_list_builder(
2925            *expressions,
2926            instance=self,
2927            arg="laterals",
2928            append=append,
2929            into=Lateral,
2930            prefix="LATERAL VIEW",
2931            dialect=dialect,
2932            copy=copy,
2933            **opts,
2934        )
2935
2936    def join(
2937        self,
2938        expression: ExpOrStr,
2939        on: t.Optional[ExpOrStr] = None,
2940        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
2941        append: bool = True,
2942        join_type: t.Optional[str] = None,
2943        join_alias: t.Optional[Identifier | str] = None,
2944        dialect: DialectType = None,
2945        copy: bool = True,
2946        **opts,
2947    ) -> Select:
2948        """
2949        Append to or set the JOIN expressions.
2950
2951        Example:
2952            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2953            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2954
2955            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2956            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2957
2958            Use `join_type` to change the type of join:
2959
2960            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2961            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2962
2963        Args:
2964            expression: the SQL code string to parse.
2965                If an `Expression` instance is passed, it will be used as-is.
2966            on: optionally specify the join "on" criteria as a SQL string.
2967                If an `Expression` instance is passed, it will be used as-is.
2968            using: optionally specify the join "using" criteria as a SQL string.
2969                If an `Expression` instance is passed, it will be used as-is.
2970            append: if `True`, add to any existing expressions.
2971                Otherwise, this resets the expressions.
2972            join_type: if set, alter the parsed join type.
2973            join_alias: an optional alias for the joined source.
2974            dialect: the dialect used to parse the input expressions.
2975            copy: if `False`, modify this expression instance in-place.
2976            opts: other options to use to parse the input expressions.
2977
2978        Returns:
2979            Select: the modified expression.
2980        """
2981        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
2982
2983        try:
2984            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2985        except ParseError:
2986            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2987
2988        join = expression if isinstance(expression, Join) else Join(this=expression)
2989
2990        if isinstance(join.this, Select):
2991            join.this.replace(join.this.subquery())
2992
2993        if join_type:
2994            method: t.Optional[Token]
2995            side: t.Optional[Token]
2996            kind: t.Optional[Token]
2997
2998            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2999
3000            if method:
3001                join.set("method", method.text)
3002            if side:
3003                join.set("side", side.text)
3004            if kind:
3005                join.set("kind", kind.text)
3006
3007        if on:
3008            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3009            join.set("on", on)
3010
3011        if using:
3012            join = _apply_list_builder(
3013                *ensure_list(using),
3014                instance=join,
3015                arg="using",
3016                append=append,
3017                copy=copy,
3018                into=Identifier,
3019                **opts,
3020            )
3021
3022        if join_alias:
3023            join.set("this", alias_(join.this, join_alias, table=True))
3024
3025        return _apply_list_builder(
3026            join,
3027            instance=self,
3028            arg="joins",
3029            append=append,
3030            copy=copy,
3031            **opts,
3032        )
3033
3034    def where(
3035        self,
3036        *expressions: t.Optional[ExpOrStr],
3037        append: bool = True,
3038        dialect: DialectType = None,
3039        copy: bool = True,
3040        **opts,
3041    ) -> Select:
3042        """
3043        Append to or set the WHERE expressions.
3044
3045        Example:
3046            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3047            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3048
3049        Args:
3050            *expressions: the SQL code strings to parse.
3051                If an `Expression` instance is passed, it will be used as-is.
3052                Multiple expressions are combined with an AND operator.
3053            append: if `True`, AND the new expressions to any existing expression.
3054                Otherwise, this resets the expression.
3055            dialect: the dialect used to parse the input expressions.
3056            copy: if `False`, modify this expression instance in-place.
3057            opts: other options to use to parse the input expressions.
3058
3059        Returns:
3060            Select: the modified expression.
3061        """
3062        return _apply_conjunction_builder(
3063            *expressions,
3064            instance=self,
3065            arg="where",
3066            append=append,
3067            into=Where,
3068            dialect=dialect,
3069            copy=copy,
3070            **opts,
3071        )
3072
3073    def having(
3074        self,
3075        *expressions: t.Optional[ExpOrStr],
3076        append: bool = True,
3077        dialect: DialectType = None,
3078        copy: bool = True,
3079        **opts,
3080    ) -> Select:
3081        """
3082        Append to or set the HAVING expressions.
3083
3084        Example:
3085            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3086            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3087
3088        Args:
3089            *expressions: the SQL code strings to parse.
3090                If an `Expression` instance is passed, it will be used as-is.
3091                Multiple expressions are combined with an AND operator.
3092            append: if `True`, AND the new expressions to any existing expression.
3093                Otherwise, this resets the expression.
3094            dialect: the dialect used to parse the input expressions.
3095            copy: if `False`, modify this expression instance in-place.
3096            opts: other options to use to parse the input expressions.
3097
3098        Returns:
3099            The modified Select expression.
3100        """
3101        return _apply_conjunction_builder(
3102            *expressions,
3103            instance=self,
3104            arg="having",
3105            append=append,
3106            into=Having,
3107            dialect=dialect,
3108            copy=copy,
3109            **opts,
3110        )
3111
3112    def window(
3113        self,
3114        *expressions: t.Optional[ExpOrStr],
3115        append: bool = True,
3116        dialect: DialectType = None,
3117        copy: bool = True,
3118        **opts,
3119    ) -> Select:
3120        return _apply_list_builder(
3121            *expressions,
3122            instance=self,
3123            arg="windows",
3124            append=append,
3125            into=Window,
3126            dialect=dialect,
3127            copy=copy,
3128            **opts,
3129        )
3130
3131    def qualify(
3132        self,
3133        *expressions: t.Optional[ExpOrStr],
3134        append: bool = True,
3135        dialect: DialectType = None,
3136        copy: bool = True,
3137        **opts,
3138    ) -> Select:
3139        return _apply_conjunction_builder(
3140            *expressions,
3141            instance=self,
3142            arg="qualify",
3143            append=append,
3144            into=Qualify,
3145            dialect=dialect,
3146            copy=copy,
3147            **opts,
3148        )
3149
3150    def distinct(
3151        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3152    ) -> Select:
3153        """
3154        Set the OFFSET expression.
3155
3156        Example:
3157            >>> Select().from_("tbl").select("x").distinct().sql()
3158            'SELECT DISTINCT x FROM tbl'
3159
3160        Args:
3161            ons: the expressions to distinct on
3162            distinct: whether the Select should be distinct
3163            copy: if `False`, modify this expression instance in-place.
3164
3165        Returns:
3166            Select: the modified expression.
3167        """
3168        instance = maybe_copy(self, copy)
3169        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3170        instance.set("distinct", Distinct(on=on) if distinct else None)
3171        return instance
3172
3173    def ctas(
3174        self,
3175        table: ExpOrStr,
3176        properties: t.Optional[t.Dict] = None,
3177        dialect: DialectType = None,
3178        copy: bool = True,
3179        **opts,
3180    ) -> Create:
3181        """
3182        Convert this expression to a CREATE TABLE AS statement.
3183
3184        Example:
3185            >>> Select().select("*").from_("tbl").ctas("x").sql()
3186            'CREATE TABLE x AS SELECT * FROM tbl'
3187
3188        Args:
3189            table: the SQL code string to parse as the table name.
3190                If another `Expression` instance is passed, it will be used as-is.
3191            properties: an optional mapping of table properties
3192            dialect: the dialect used to parse the input table.
3193            copy: if `False`, modify this expression instance in-place.
3194            opts: other options to use to parse the input table.
3195
3196        Returns:
3197            The new Create expression.
3198        """
3199        instance = maybe_copy(self, copy)
3200        table_expression = maybe_parse(
3201            table,
3202            into=Table,
3203            dialect=dialect,
3204            **opts,
3205        )
3206        properties_expression = None
3207        if properties:
3208            properties_expression = Properties.from_dict(properties)
3209
3210        return Create(
3211            this=table_expression,
3212            kind="table",
3213            expression=instance,
3214            properties=properties_expression,
3215        )
3216
3217    def lock(self, update: bool = True, copy: bool = True) -> Select:
3218        """
3219        Set the locking read mode for this expression.
3220
3221        Examples:
3222            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3223            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3224
3225            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3226            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3227
3228        Args:
3229            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3230            copy: if `False`, modify this expression instance in-place.
3231
3232        Returns:
3233            The modified expression.
3234        """
3235        inst = maybe_copy(self, copy)
3236        inst.set("locks", [Lock(update=update)])
3237
3238        return inst
3239
3240    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3241        """
3242        Set hints for this expression.
3243
3244        Examples:
3245            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3246            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3247
3248        Args:
3249            hints: The SQL code strings to parse as the hints.
3250                If an `Expression` instance is passed, it will be used as-is.
3251            dialect: The dialect used to parse the hints.
3252            copy: If `False`, modify this expression instance in-place.
3253
3254        Returns:
3255            The modified expression.
3256        """
3257        inst = maybe_copy(self, copy)
3258        inst.set(
3259            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3260        )
3261
3262        return inst
3263
3264    @property
3265    def named_selects(self) -> t.List[str]:
3266        return [e.output_name for e in self.expressions if e.alias_or_name]
3267
3268    @property
3269    def is_star(self) -> bool:
3270        return any(expression.is_star for expression in self.expressions)
3271
3272    @property
3273    def selects(self) -> t.List[Expression]:
3274        return self.expressions
3275
3276
3277class Subquery(DerivedTable, Unionable):
3278    arg_types = {
3279        "this": True,
3280        "alias": False,
3281        "with": False,
3282        **QUERY_MODIFIERS,
3283    }
3284
3285    def unnest(self):
3286        """
3287        Returns the first non subquery.
3288        """
3289        expression = self
3290        while isinstance(expression, Subquery):
3291            expression = expression.this
3292        return expression
3293
3294    def unwrap(self) -> Subquery:
3295        expression = self
3296        while expression.same_parent and expression.is_wrapper:
3297            expression = t.cast(Subquery, expression.parent)
3298        return expression
3299
3300    @property
3301    def is_wrapper(self) -> bool:
3302        """
3303        Whether this Subquery acts as a simple wrapper around another expression.
3304
3305        SELECT * FROM (((SELECT * FROM t)))
3306                      ^
3307                      This corresponds to a "wrapper" Subquery node
3308        """
3309        return all(v is None for k, v in self.args.items() if k != "this")
3310
3311    @property
3312    def is_star(self) -> bool:
3313        return self.this.is_star
3314
3315    @property
3316    def output_name(self) -> str:
3317        return self.alias
3318
3319
3320class TableSample(Expression):
3321    arg_types = {
3322        "this": False,
3323        "method": False,
3324        "bucket_numerator": False,
3325        "bucket_denominator": False,
3326        "bucket_field": False,
3327        "percent": False,
3328        "rows": False,
3329        "size": False,
3330        "seed": False,
3331        "kind": False,
3332    }
3333
3334
3335class Tag(Expression):
3336    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3337
3338    arg_types = {
3339        "this": False,
3340        "prefix": False,
3341        "postfix": False,
3342    }
3343
3344
3345# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3346# https://duckdb.org/docs/sql/statements/pivot
3347class Pivot(Expression):
3348    arg_types = {
3349        "this": False,
3350        "alias": False,
3351        "expressions": True,
3352        "field": False,
3353        "unpivot": False,
3354        "using": False,
3355        "group": False,
3356        "columns": False,
3357        "include_nulls": False,
3358    }
3359
3360
3361class Window(Condition):
3362    arg_types = {
3363        "this": True,
3364        "partition_by": False,
3365        "order": False,
3366        "spec": False,
3367        "alias": False,
3368        "over": False,
3369        "first": False,
3370    }
3371
3372
3373class WindowSpec(Expression):
3374    arg_types = {
3375        "kind": False,
3376        "start": False,
3377        "start_side": False,
3378        "end": False,
3379        "end_side": False,
3380    }
3381
3382
3383class Where(Expression):
3384    pass
3385
3386
3387class Star(Expression):
3388    arg_types = {"except": False, "replace": False}
3389
3390    @property
3391    def name(self) -> str:
3392        return "*"
3393
3394    @property
3395    def output_name(self) -> str:
3396        return self.name
3397
3398
3399class Parameter(Condition):
3400    arg_types = {"this": True, "wrapped": False}
3401
3402
3403class SessionParameter(Condition):
3404    arg_types = {"this": True, "kind": False}
3405
3406
3407class Placeholder(Condition):
3408    arg_types = {"this": False, "kind": False}
3409
3410
3411class Null(Condition):
3412    arg_types: t.Dict[str, t.Any] = {}
3413
3414    @property
3415    def name(self) -> str:
3416        return "NULL"
3417
3418
3419class Boolean(Condition):
3420    pass
3421
3422
3423class DataTypeParam(Expression):
3424    arg_types = {"this": True, "expression": False}
3425
3426
3427class DataType(Expression):
3428    arg_types = {
3429        "this": True,
3430        "expressions": False,
3431        "nested": False,
3432        "values": False,
3433        "prefix": False,
3434        "kind": False,
3435    }
3436
3437    class Type(AutoName):
3438        ARRAY = auto()
3439        BIGDECIMAL = auto()
3440        BIGINT = auto()
3441        BIGSERIAL = auto()
3442        BINARY = auto()
3443        BIT = auto()
3444        BOOLEAN = auto()
3445        CHAR = auto()
3446        DATE = auto()
3447        DATEMULTIRANGE = auto()
3448        DATERANGE = auto()
3449        DATETIME = auto()
3450        DATETIME64 = auto()
3451        DECIMAL = auto()
3452        DOUBLE = auto()
3453        ENUM = auto()
3454        ENUM8 = auto()
3455        ENUM16 = auto()
3456        FIXEDSTRING = auto()
3457        FLOAT = auto()
3458        GEOGRAPHY = auto()
3459        GEOMETRY = auto()
3460        HLLSKETCH = auto()
3461        HSTORE = auto()
3462        IMAGE = auto()
3463        INET = auto()
3464        INT = auto()
3465        INT128 = auto()
3466        INT256 = auto()
3467        INT4MULTIRANGE = auto()
3468        INT4RANGE = auto()
3469        INT8MULTIRANGE = auto()
3470        INT8RANGE = auto()
3471        INTERVAL = auto()
3472        IPADDRESS = auto()
3473        IPPREFIX = auto()
3474        JSON = auto()
3475        JSONB = auto()
3476        LONGBLOB = auto()
3477        LONGTEXT = auto()
3478        LOWCARDINALITY = auto()
3479        MAP = auto()
3480        MEDIUMBLOB = auto()
3481        MEDIUMINT = auto()
3482        MEDIUMTEXT = auto()
3483        MONEY = auto()
3484        NCHAR = auto()
3485        NESTED = auto()
3486        NULL = auto()
3487        NULLABLE = auto()
3488        NUMMULTIRANGE = auto()
3489        NUMRANGE = auto()
3490        NVARCHAR = auto()
3491        OBJECT = auto()
3492        ROWVERSION = auto()
3493        SERIAL = auto()
3494        SET = auto()
3495        SMALLINT = auto()
3496        SMALLMONEY = auto()
3497        SMALLSERIAL = auto()
3498        STRUCT = auto()
3499        SUPER = auto()
3500        TEXT = auto()
3501        TIME = auto()
3502        TIMETZ = auto()
3503        TIMESTAMP = auto()
3504        TIMESTAMPLTZ = auto()
3505        TIMESTAMPTZ = auto()
3506        TINYINT = auto()
3507        TSMULTIRANGE = auto()
3508        TSRANGE = auto()
3509        TSTZMULTIRANGE = auto()
3510        TSTZRANGE = auto()
3511        UBIGINT = auto()
3512        UINT = auto()
3513        UINT128 = auto()
3514        UINT256 = auto()
3515        UNIQUEIDENTIFIER = auto()
3516        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3517        USERDEFINED = "USER-DEFINED"
3518        USMALLINT = auto()
3519        UTINYINT = auto()
3520        UUID = auto()
3521        VARBINARY = auto()
3522        VARCHAR = auto()
3523        VARIANT = auto()
3524        XML = auto()
3525        YEAR = auto()
3526
3527    TEXT_TYPES = {
3528        Type.CHAR,
3529        Type.NCHAR,
3530        Type.VARCHAR,
3531        Type.NVARCHAR,
3532        Type.TEXT,
3533    }
3534
3535    INTEGER_TYPES = {
3536        Type.INT,
3537        Type.TINYINT,
3538        Type.SMALLINT,
3539        Type.BIGINT,
3540        Type.INT128,
3541        Type.INT256,
3542    }
3543
3544    FLOAT_TYPES = {
3545        Type.FLOAT,
3546        Type.DOUBLE,
3547    }
3548
3549    NUMERIC_TYPES = {
3550        *INTEGER_TYPES,
3551        *FLOAT_TYPES,
3552    }
3553
3554    TEMPORAL_TYPES = {
3555        Type.TIME,
3556        Type.TIMETZ,
3557        Type.TIMESTAMP,
3558        Type.TIMESTAMPTZ,
3559        Type.TIMESTAMPLTZ,
3560        Type.DATE,
3561        Type.DATETIME,
3562        Type.DATETIME64,
3563    }
3564
3565    @classmethod
3566    def build(
3567        cls,
3568        dtype: str | DataType | DataType.Type,
3569        dialect: DialectType = None,
3570        udt: bool = False,
3571        **kwargs,
3572    ) -> DataType:
3573        """
3574        Constructs a DataType object.
3575
3576        Args:
3577            dtype: the data type of interest.
3578            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3579            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3580                DataType, thus creating a user-defined type.
3581            kawrgs: additional arguments to pass in the constructor of DataType.
3582
3583        Returns:
3584            The constructed DataType object.
3585        """
3586        from sqlglot import parse_one
3587
3588        if isinstance(dtype, str):
3589            if dtype.upper() == "UNKNOWN":
3590                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3591
3592            try:
3593                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3594            except ParseError:
3595                if udt:
3596                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3597                raise
3598        elif isinstance(dtype, DataType.Type):
3599            data_type_exp = DataType(this=dtype)
3600        elif isinstance(dtype, DataType):
3601            return dtype
3602        else:
3603            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3604
3605        return DataType(**{**data_type_exp.args, **kwargs})
3606
3607    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3608        """
3609        Checks whether this DataType matches one of the provided data types. Nested types or precision
3610        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3611
3612        Args:
3613            dtypes: the data types to compare this DataType to.
3614
3615        Returns:
3616            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3617        """
3618        for dtype in dtypes:
3619            other = DataType.build(dtype, udt=True)
3620
3621            if (
3622                other.expressions
3623                or self.this == DataType.Type.USERDEFINED
3624                or other.this == DataType.Type.USERDEFINED
3625            ):
3626                matches = self == other
3627            else:
3628                matches = self.this == other.this
3629
3630            if matches:
3631                return True
3632        return False
3633
3634
3635# https://www.postgresql.org/docs/15/datatype-pseudo.html
3636class PseudoType(Expression):
3637    pass
3638
3639
3640# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3641class SubqueryPredicate(Predicate):
3642    pass
3643
3644
3645class All(SubqueryPredicate):
3646    pass
3647
3648
3649class Any(SubqueryPredicate):
3650    pass
3651
3652
3653class Exists(SubqueryPredicate):
3654    pass
3655
3656
3657# Commands to interact with the databases or engines. For most of the command
3658# expressions we parse whatever comes after the command's name as a string.
3659class Command(Expression):
3660    arg_types = {"this": True, "expression": False}
3661
3662
3663class Transaction(Expression):
3664    arg_types = {"this": False, "modes": False, "mark": False}
3665
3666
3667class Commit(Expression):
3668    arg_types = {"chain": False, "this": False, "durability": False}
3669
3670
3671class Rollback(Expression):
3672    arg_types = {"savepoint": False, "this": False}
3673
3674
3675class AlterTable(Expression):
3676    arg_types = {"this": True, "actions": True, "exists": False}
3677
3678
3679class AddConstraint(Expression):
3680    arg_types = {"this": False, "expression": False, "enforced": False}
3681
3682
3683class DropPartition(Expression):
3684    arg_types = {"expressions": True, "exists": False}
3685
3686
3687# Binary expressions like (ADD a b)
3688class Binary(Condition):
3689    arg_types = {"this": True, "expression": True}
3690
3691    @property
3692    def left(self):
3693        return self.this
3694
3695    @property
3696    def right(self):
3697        return self.expression
3698
3699
3700class Add(Binary):
3701    pass
3702
3703
3704class Connector(Binary):
3705    pass
3706
3707
3708class And(Connector):
3709    pass
3710
3711
3712class Or(Connector):
3713    pass
3714
3715
3716class BitwiseAnd(Binary):
3717    pass
3718
3719
3720class BitwiseLeftShift(Binary):
3721    pass
3722
3723
3724class BitwiseOr(Binary):
3725    pass
3726
3727
3728class BitwiseRightShift(Binary):
3729    pass
3730
3731
3732class BitwiseXor(Binary):
3733    pass
3734
3735
3736class Div(Binary):
3737    pass
3738
3739
3740class Overlaps(Binary):
3741    pass
3742
3743
3744class Dot(Binary):
3745    @property
3746    def name(self) -> str:
3747        return self.expression.name
3748
3749    @property
3750    def output_name(self) -> str:
3751        return self.name
3752
3753    @classmethod
3754    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3755        """Build a Dot object with a sequence of expressions."""
3756        if len(expressions) < 2:
3757            raise ValueError(f"Dot requires >= 2 expressions.")
3758
3759        a, b, *expressions = expressions
3760        dot = Dot(this=a, expression=b)
3761
3762        for expression in expressions:
3763            dot = Dot(this=dot, expression=expression)
3764
3765        return dot
3766
3767
3768class DPipe(Binary):
3769    pass
3770
3771
3772class SafeDPipe(DPipe):
3773    pass
3774
3775
3776class EQ(Binary, Predicate):
3777    pass
3778
3779
3780class NullSafeEQ(Binary, Predicate):
3781    pass
3782
3783
3784class NullSafeNEQ(Binary, Predicate):
3785    pass
3786
3787
3788class Distance(Binary):
3789    pass
3790
3791
3792class Escape(Binary):
3793    pass
3794
3795
3796class Glob(Binary, Predicate):
3797    pass
3798
3799
3800class GT(Binary, Predicate):
3801    pass
3802
3803
3804class GTE(Binary, Predicate):
3805    pass
3806
3807
3808class ILike(Binary, Predicate):
3809    pass
3810
3811
3812class ILikeAny(Binary, Predicate):
3813    pass
3814
3815
3816class IntDiv(Binary):
3817    pass
3818
3819
3820class Is(Binary, Predicate):
3821    pass
3822
3823
3824class Kwarg(Binary):
3825    """Kwarg in special functions like func(kwarg => y)."""
3826
3827
3828class Like(Binary, Predicate):
3829    pass
3830
3831
3832class LikeAny(Binary, Predicate):
3833    pass
3834
3835
3836class LT(Binary, Predicate):
3837    pass
3838
3839
3840class LTE(Binary, Predicate):
3841    pass
3842
3843
3844class Mod(Binary):
3845    pass
3846
3847
3848class Mul(Binary):
3849    pass
3850
3851
3852class NEQ(Binary, Predicate):
3853    pass
3854
3855
3856class SimilarTo(Binary, Predicate):
3857    pass
3858
3859
3860class Slice(Binary):
3861    arg_types = {"this": False, "expression": False}
3862
3863
3864class Sub(Binary):
3865    pass
3866
3867
3868class ArrayOverlaps(Binary):
3869    pass
3870
3871
3872# Unary Expressions
3873# (NOT a)
3874class Unary(Condition):
3875    pass
3876
3877
3878class BitwiseNot(Unary):
3879    pass
3880
3881
3882class Not(Unary):
3883    pass
3884
3885
3886class Paren(Unary):
3887    arg_types = {"this": True, "with": False}
3888
3889    @property
3890    def output_name(self) -> str:
3891        return self.this.name
3892
3893
3894class Neg(Unary):
3895    pass
3896
3897
3898class Alias(Expression):
3899    arg_types = {"this": True, "alias": False}
3900
3901    @property
3902    def output_name(self) -> str:
3903        return self.alias
3904
3905
3906class Aliases(Expression):
3907    arg_types = {"this": True, "expressions": True}
3908
3909    @property
3910    def aliases(self):
3911        return self.expressions
3912
3913
3914class AtTimeZone(Expression):
3915    arg_types = {"this": True, "zone": True}
3916
3917
3918class Between(Predicate):
3919    arg_types = {"this": True, "low": True, "high": True}
3920
3921
3922class Bracket(Condition):
3923    arg_types = {"this": True, "expressions": True}
3924
3925
3926class SafeBracket(Bracket):
3927    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
3928
3929
3930class Distinct(Expression):
3931    arg_types = {"expressions": False, "on": False}
3932
3933
3934class In(Predicate):
3935    arg_types = {
3936        "this": True,
3937        "expressions": False,
3938        "query": False,
3939        "unnest": False,
3940        "field": False,
3941        "is_global": False,
3942    }
3943
3944
3945class TimeUnit(Expression):
3946    """Automatically converts unit arg into a var."""
3947
3948    arg_types = {"unit": False}
3949
3950    def __init__(self, **args):
3951        unit = args.get("unit")
3952        if isinstance(unit, (Column, Literal)):
3953            args["unit"] = Var(this=unit.name)
3954        elif isinstance(unit, Week):
3955            unit.set("this", Var(this=unit.this.name))
3956
3957        super().__init__(**args)
3958
3959
3960# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3961# https://trino.io/docs/current/language/types.html#interval-year-to-month
3962class IntervalYearToMonthSpan(Expression):
3963    arg_types = {}
3964
3965
3966# https://www.oracletutorial.com/oracle-basics/oracle-interval/
3967# https://trino.io/docs/current/language/types.html#interval-day-to-second
3968class IntervalDayToSecondSpan(Expression):
3969    arg_types = {}
3970
3971
3972class Interval(TimeUnit):
3973    arg_types = {"this": False, "unit": False}
3974
3975    @property
3976    def unit(self) -> t.Optional[Var]:
3977        return self.args.get("unit")
3978
3979
3980class IgnoreNulls(Expression):
3981    pass
3982
3983
3984class RespectNulls(Expression):
3985    pass
3986
3987
3988# Functions
3989class Func(Condition):
3990    """
3991    The base class for all function expressions.
3992
3993    Attributes:
3994        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3995            treated as a variable length argument and the argument's value will be stored as a list.
3996        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3997            for this function expression. These values are used to map this node to a name during parsing
3998            as well as to provide the function's name during SQL string generation. By default the SQL
3999            name is set to the expression's class name transformed to snake case.
4000    """
4001
4002    is_var_len_args = False
4003
4004    @classmethod
4005    def from_arg_list(cls, args):
4006        if cls.is_var_len_args:
4007            all_arg_keys = list(cls.arg_types)
4008            # If this function supports variable length argument treat the last argument as such.
4009            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4010            num_non_var = len(non_var_len_arg_keys)
4011
4012            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4013            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4014        else:
4015            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4016
4017        return cls(**args_dict)
4018
4019    @classmethod
4020    def sql_names(cls):
4021        if cls is Func:
4022            raise NotImplementedError(
4023                "SQL name is only supported by concrete function implementations"
4024            )
4025        if "_sql_names" not in cls.__dict__:
4026            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4027        return cls._sql_names
4028
4029    @classmethod
4030    def sql_name(cls):
4031        return cls.sql_names()[0]
4032
4033    @classmethod
4034    def default_parser_mappings(cls):
4035        return {name: cls.from_arg_list for name in cls.sql_names()}
4036
4037
4038class AggFunc(Func):
4039    pass
4040
4041
4042class ParameterizedAgg(AggFunc):
4043    arg_types = {"this": True, "expressions": True, "params": True}
4044
4045
4046class Abs(Func):
4047    pass
4048
4049
4050# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4051class Transform(Func):
4052    arg_types = {"this": True, "expression": True}
4053
4054
4055class Anonymous(Func):
4056    arg_types = {"this": True, "expressions": False}
4057    is_var_len_args = True
4058
4059
4060# https://docs.snowflake.com/en/sql-reference/functions/hll
4061# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4062class Hll(AggFunc):
4063    arg_types = {"this": True, "expressions": False}
4064    is_var_len_args = True
4065
4066
4067class ApproxDistinct(AggFunc):
4068    arg_types = {"this": True, "accuracy": False}
4069    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4070
4071
4072class Array(Func):
4073    arg_types = {"expressions": False}
4074    is_var_len_args = True
4075
4076
4077# https://docs.snowflake.com/en/sql-reference/functions/to_char
4078class ToChar(Func):
4079    arg_types = {"this": True, "format": False}
4080
4081
4082class GenerateSeries(Func):
4083    arg_types = {"start": True, "end": True, "step": False}
4084
4085
4086class ArrayAgg(AggFunc):
4087    pass
4088
4089
4090class ArrayAll(Func):
4091    arg_types = {"this": True, "expression": True}
4092
4093
4094class ArrayAny(Func):
4095    arg_types = {"this": True, "expression": True}
4096
4097
4098class ArrayConcat(Func):
4099    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4100    arg_types = {"this": True, "expressions": False}
4101    is_var_len_args = True
4102
4103
4104class ArrayContains(Binary, Func):
4105    pass
4106
4107
4108class ArrayContained(Binary):
4109    pass
4110
4111
4112class ArrayFilter(Func):
4113    arg_types = {"this": True, "expression": True}
4114    _sql_names = ["FILTER", "ARRAY_FILTER"]
4115
4116
4117class ArrayJoin(Func):
4118    arg_types = {"this": True, "expression": True, "null": False}
4119
4120
4121class ArraySize(Func):
4122    arg_types = {"this": True, "expression": False}
4123
4124
4125class ArraySort(Func):
4126    arg_types = {"this": True, "expression": False}
4127
4128
4129class ArraySum(Func):
4130    pass
4131
4132
4133class ArrayUnionAgg(AggFunc):
4134    pass
4135
4136
4137class Avg(AggFunc):
4138    pass
4139
4140
4141class AnyValue(AggFunc):
4142    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4143
4144
4145class First(Func):
4146    arg_types = {"this": True, "ignore_nulls": False}
4147
4148
4149class Last(Func):
4150    arg_types = {"this": True, "ignore_nulls": False}
4151
4152
4153class Case(Func):
4154    arg_types = {"this": False, "ifs": True, "default": False}
4155
4156    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4157        instance = maybe_copy(self, copy)
4158        instance.append(
4159            "ifs",
4160            If(
4161                this=maybe_parse(condition, copy=copy, **opts),
4162                true=maybe_parse(then, copy=copy, **opts),
4163            ),
4164        )
4165        return instance
4166
4167    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4168        instance = maybe_copy(self, copy)
4169        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4170        return instance
4171
4172
4173class Cast(Func):
4174    arg_types = {"this": True, "to": True, "format": False}
4175
4176    @property
4177    def name(self) -> str:
4178        return self.this.name
4179
4180    @property
4181    def to(self) -> DataType:
4182        return self.args["to"]
4183
4184    @property
4185    def output_name(self) -> str:
4186        return self.name
4187
4188    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4189        """
4190        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4191        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4192        array<int> != array<float>.
4193
4194        Args:
4195            dtypes: the data types to compare this Cast's DataType to.
4196
4197        Returns:
4198            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4199        """
4200        return self.to.is_type(*dtypes)
4201
4202
4203class TryCast(Cast):
4204    pass
4205
4206
4207class CastToStrType(Func):
4208    arg_types = {"this": True, "to": True}
4209
4210
4211class Collate(Binary):
4212    pass
4213
4214
4215class Ceil(Func):
4216    arg_types = {"this": True, "decimals": False}
4217    _sql_names = ["CEIL", "CEILING"]
4218
4219
4220class Coalesce(Func):
4221    arg_types = {"this": True, "expressions": False}
4222    is_var_len_args = True
4223    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4224
4225
4226class Concat(Func):
4227    arg_types = {"expressions": True}
4228    is_var_len_args = True
4229
4230
4231class SafeConcat(Concat):
4232    pass
4233
4234
4235class ConcatWs(Concat):
4236    _sql_names = ["CONCAT_WS"]
4237
4238
4239class Count(AggFunc):
4240    arg_types = {"this": False, "expressions": False}
4241    is_var_len_args = True
4242
4243
4244class CountIf(AggFunc):
4245    pass
4246
4247
4248class CurrentDate(Func):
4249    arg_types = {"this": False}
4250
4251
4252class CurrentDatetime(Func):
4253    arg_types = {"this": False}
4254
4255
4256class CurrentTime(Func):
4257    arg_types = {"this": False}
4258
4259
4260class CurrentTimestamp(Func):
4261    arg_types = {"this": False}
4262
4263
4264class CurrentUser(Func):
4265    arg_types = {"this": False}
4266
4267
4268class DateAdd(Func, TimeUnit):
4269    arg_types = {"this": True, "expression": True, "unit": False}
4270
4271
4272class DateSub(Func, TimeUnit):
4273    arg_types = {"this": True, "expression": True, "unit": False}
4274
4275
4276class DateDiff(Func, TimeUnit):
4277    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4278    arg_types = {"this": True, "expression": True, "unit": False}
4279
4280
4281class DateTrunc(Func):
4282    arg_types = {"unit": True, "this": True, "zone": False}
4283
4284
4285class DatetimeAdd(Func, TimeUnit):
4286    arg_types = {"this": True, "expression": True, "unit": False}
4287
4288
4289class DatetimeSub(Func, TimeUnit):
4290    arg_types = {"this": True, "expression": True, "unit": False}
4291
4292
4293class DatetimeDiff(Func, TimeUnit):
4294    arg_types = {"this": True, "expression": True, "unit": False}
4295
4296
4297class DatetimeTrunc(Func, TimeUnit):
4298    arg_types = {"this": True, "unit": True, "zone": False}
4299
4300
4301class DayOfWeek(Func):
4302    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4303
4304
4305class DayOfMonth(Func):
4306    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4307
4308
4309class DayOfYear(Func):
4310    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4311
4312
4313class WeekOfYear(Func):
4314    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4315
4316
4317class MonthsBetween(Func):
4318    arg_types = {"this": True, "expression": True, "roundoff": False}
4319
4320
4321class LastDateOfMonth(Func):
4322    pass
4323
4324
4325class Extract(Func):
4326    arg_types = {"this": True, "expression": True}
4327
4328
4329class TimestampAdd(Func, TimeUnit):
4330    arg_types = {"this": True, "expression": True, "unit": False}
4331
4332
4333class TimestampSub(Func, TimeUnit):
4334    arg_types = {"this": True, "expression": True, "unit": False}
4335
4336
4337class TimestampDiff(Func, TimeUnit):
4338    arg_types = {"this": True, "expression": True, "unit": False}
4339
4340
4341class TimestampTrunc(Func, TimeUnit):
4342    arg_types = {"this": True, "unit": True, "zone": False}
4343
4344
4345class TimeAdd(Func, TimeUnit):
4346    arg_types = {"this": True, "expression": True, "unit": False}
4347
4348
4349class TimeSub(Func, TimeUnit):
4350    arg_types = {"this": True, "expression": True, "unit": False}
4351
4352
4353class TimeDiff(Func, TimeUnit):
4354    arg_types = {"this": True, "expression": True, "unit": False}
4355
4356
4357class TimeTrunc(Func, TimeUnit):
4358    arg_types = {"this": True, "unit": True, "zone": False}
4359
4360
4361class DateFromParts(Func):
4362    _sql_names = ["DATEFROMPARTS"]
4363    arg_types = {"year": True, "month": True, "day": True}
4364
4365
4366class DateStrToDate(Func):
4367    pass
4368
4369
4370class DateToDateStr(Func):
4371    pass
4372
4373
4374class DateToDi(Func):
4375    pass
4376
4377
4378# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4379class Date(Func):
4380    arg_types = {"this": True, "zone": False}
4381
4382
4383class Day(Func):
4384    pass
4385
4386
4387class Decode(Func):
4388    arg_types = {"this": True, "charset": True, "replace": False}
4389
4390
4391class DiToDate(Func):
4392    pass
4393
4394
4395class Encode(Func):
4396    arg_types = {"this": True, "charset": True}
4397
4398
4399class Exp(Func):
4400    pass
4401
4402
4403class Explode(Func):
4404    pass
4405
4406
4407class Floor(Func):
4408    arg_types = {"this": True, "decimals": False}
4409
4410
4411class FromBase64(Func):
4412    pass
4413
4414
4415class ToBase64(Func):
4416    pass
4417
4418
4419class Greatest(Func):
4420    arg_types = {"this": True, "expressions": False}
4421    is_var_len_args = True
4422
4423
4424class GroupConcat(Func):
4425    arg_types = {"this": True, "separator": False}
4426
4427
4428class Hex(Func):
4429    pass
4430
4431
4432class Xor(Connector, Func):
4433    arg_types = {"this": False, "expression": False, "expressions": False}
4434
4435
4436class If(Func):
4437    arg_types = {"this": True, "true": True, "false": False}
4438
4439
4440class Initcap(Func):
4441    arg_types = {"this": True, "expression": False}
4442
4443
4444class IsNan(Func):
4445    _sql_names = ["IS_NAN", "ISNAN"]
4446
4447
4448class JSONKeyValue(Expression):
4449    arg_types = {"this": True, "expression": True}
4450
4451
4452class JSONObject(Func):
4453    arg_types = {
4454        "expressions": False,
4455        "null_handling": False,
4456        "unique_keys": False,
4457        "return_type": False,
4458        "format_json": False,
4459        "encoding": False,
4460    }
4461
4462
4463class OpenJSONColumnDef(Expression):
4464    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4465
4466
4467class OpenJSON(Func):
4468    arg_types = {"this": True, "path": False, "expressions": False}
4469
4470
4471class JSONBContains(Binary):
4472    _sql_names = ["JSONB_CONTAINS"]
4473
4474
4475class JSONExtract(Binary, Func):
4476    _sql_names = ["JSON_EXTRACT"]
4477
4478
4479class JSONExtractScalar(JSONExtract):
4480    _sql_names = ["JSON_EXTRACT_SCALAR"]
4481
4482
4483class JSONBExtract(JSONExtract):
4484    _sql_names = ["JSONB_EXTRACT"]
4485
4486
4487class JSONBExtractScalar(JSONExtract):
4488    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4489
4490
4491class JSONFormat(Func):
4492    arg_types = {"this": False, "options": False}
4493    _sql_names = ["JSON_FORMAT"]
4494
4495
4496# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4497class JSONArrayContains(Binary, Predicate, Func):
4498    _sql_names = ["JSON_ARRAY_CONTAINS"]
4499
4500
4501class Least(Func):
4502    arg_types = {"this": True, "expressions": False}
4503    is_var_len_args = True
4504
4505
4506class Left(Func):
4507    arg_types = {"this": True, "expression": True}
4508
4509
4510class Right(Func):
4511    arg_types = {"this": True, "expression": True}
4512
4513
4514class Length(Func):
4515    _sql_names = ["LENGTH", "LEN"]
4516
4517
4518class Levenshtein(Func):
4519    arg_types = {
4520        "this": True,
4521        "expression": False,
4522        "ins_cost": False,
4523        "del_cost": False,
4524        "sub_cost": False,
4525    }
4526
4527
4528class Ln(Func):
4529    pass
4530
4531
4532class Log(Func):
4533    arg_types = {"this": True, "expression": False}
4534
4535
4536class Log2(Func):
4537    pass
4538
4539
4540class Log10(Func):
4541    pass
4542
4543
4544class LogicalOr(AggFunc):
4545    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4546
4547
4548class LogicalAnd(AggFunc):
4549    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4550
4551
4552class Lower(Func):
4553    _sql_names = ["LOWER", "LCASE"]
4554
4555
4556class Map(Func):
4557    arg_types = {"keys": False, "values": False}
4558
4559
4560class MapFromEntries(Func):
4561    pass
4562
4563
4564class StarMap(Func):
4565    pass
4566
4567
4568class VarMap(Func):
4569    arg_types = {"keys": True, "values": True}
4570    is_var_len_args = True
4571
4572    @property
4573    def keys(self) -> t.List[Expression]:
4574        return self.args["keys"].expressions
4575
4576    @property
4577    def values(self) -> t.List[Expression]:
4578        return self.args["values"].expressions
4579
4580
4581# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4582class MatchAgainst(Func):
4583    arg_types = {"this": True, "expressions": True, "modifier": False}
4584
4585
4586class Max(AggFunc):
4587    arg_types = {"this": True, "expressions": False}
4588    is_var_len_args = True
4589
4590
4591class MD5(Func):
4592    _sql_names = ["MD5"]
4593
4594
4595# Represents the variant of the MD5 function that returns a binary value
4596class MD5Digest(Func):
4597    _sql_names = ["MD5_DIGEST"]
4598
4599
4600class Min(AggFunc):
4601    arg_types = {"this": True, "expressions": False}
4602    is_var_len_args = True
4603
4604
4605class Month(Func):
4606    pass
4607
4608
4609class Nvl2(Func):
4610    arg_types = {"this": True, "true": True, "false": False}
4611
4612
4613class Posexplode(Func):
4614    pass
4615
4616
4617class Pow(Binary, Func):
4618    _sql_names = ["POWER", "POW"]
4619
4620
4621class PercentileCont(AggFunc):
4622    arg_types = {"this": True, "expression": False}
4623
4624
4625class PercentileDisc(AggFunc):
4626    arg_types = {"this": True, "expression": False}
4627
4628
4629class Quantile(AggFunc):
4630    arg_types = {"this": True, "quantile": True}
4631
4632
4633class ApproxQuantile(Quantile):
4634    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4635
4636
4637class RangeN(Func):
4638    arg_types = {"this": True, "expressions": True, "each": False}
4639
4640
4641class ReadCSV(Func):
4642    _sql_names = ["READ_CSV"]
4643    is_var_len_args = True
4644    arg_types = {"this": True, "expressions": False}
4645
4646
4647class Reduce(Func):
4648    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4649
4650
4651class RegexpExtract(Func):
4652    arg_types = {
4653        "this": True,
4654        "expression": True,
4655        "position": False,
4656        "occurrence": False,
4657        "parameters": False,
4658        "group": False,
4659    }
4660
4661
4662class RegexpReplace(Func):
4663    arg_types = {
4664        "this": True,
4665        "expression": True,
4666        "replacement": True,
4667        "position": False,
4668        "occurrence": False,
4669        "parameters": False,
4670    }
4671
4672
4673class RegexpLike(Binary, Func):
4674    arg_types = {"this": True, "expression": True, "flag": False}
4675
4676
4677class RegexpILike(Func):
4678    arg_types = {"this": True, "expression": True, "flag": False}
4679
4680
4681# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4682# limit is the number of times a pattern is applied
4683class RegexpSplit(Func):
4684    arg_types = {"this": True, "expression": True, "limit": False}
4685
4686
4687class Repeat(Func):
4688    arg_types = {"this": True, "times": True}
4689
4690
4691class Round(Func):
4692    arg_types = {"this": True, "decimals": False}
4693
4694
4695class RowNumber(Func):
4696    arg_types: t.Dict[str, t.Any] = {}
4697
4698
4699class SafeDivide(Func):
4700    arg_types = {"this": True, "expression": True}
4701
4702
4703class SetAgg(AggFunc):
4704    pass
4705
4706
4707class SHA(Func):
4708    _sql_names = ["SHA", "SHA1"]
4709
4710
4711class SHA2(Func):
4712    _sql_names = ["SHA2"]
4713    arg_types = {"this": True, "length": False}
4714
4715
4716class SortArray(Func):
4717    arg_types = {"this": True, "asc": False}
4718
4719
4720class Split(Func):
4721    arg_types = {"this": True, "expression": True, "limit": False}
4722
4723
4724# Start may be omitted in the case of postgres
4725# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4726class Substring(Func):
4727    arg_types = {"this": True, "start": False, "length": False}
4728
4729
4730class StandardHash(Func):
4731    arg_types = {"this": True, "expression": False}
4732
4733
4734class StartsWith(Func):
4735    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4736    arg_types = {"this": True, "expression": True}
4737
4738
4739class StrPosition(Func):
4740    arg_types = {
4741        "this": True,
4742        "substr": True,
4743        "position": False,
4744        "instance": False,
4745    }
4746
4747
4748class StrToDate(Func):
4749    arg_types = {"this": True, "format": True}
4750
4751
4752class StrToTime(Func):
4753    arg_types = {"this": True, "format": True, "zone": False}
4754
4755
4756# Spark allows unix_timestamp()
4757# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4758class StrToUnix(Func):
4759    arg_types = {"this": False, "format": False}
4760
4761
4762# https://prestodb.io/docs/current/functions/string.html
4763# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4764class StrToMap(Func):
4765    arg_types = {
4766        "this": True,
4767        "pair_delim": False,
4768        "key_value_delim": False,
4769        "duplicate_resolution_callback": False,
4770    }
4771
4772
4773class NumberToStr(Func):
4774    arg_types = {"this": True, "format": True, "culture": False}
4775
4776
4777class FromBase(Func):
4778    arg_types = {"this": True, "expression": True}
4779
4780
4781class Struct(Func):
4782    arg_types = {"expressions": True}
4783    is_var_len_args = True
4784
4785
4786class StructExtract(Func):
4787    arg_types = {"this": True, "expression": True}
4788
4789
4790# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4791# https://docs.snowflake.com/en/sql-reference/functions/insert
4792class Stuff(Func):
4793    _sql_names = ["STUFF", "INSERT"]
4794    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4795
4796
4797class Sum(AggFunc):
4798    pass
4799
4800
4801class Sqrt(Func):
4802    pass
4803
4804
4805class Stddev(AggFunc):
4806    pass
4807
4808
4809class StddevPop(AggFunc):
4810    pass
4811
4812
4813class StddevSamp(AggFunc):
4814    pass
4815
4816
4817class TimeToStr(Func):
4818    arg_types = {"this": True, "format": True, "culture": False}
4819
4820
4821class TimeToTimeStr(Func):
4822    pass
4823
4824
4825class TimeToUnix(Func):
4826    pass
4827
4828
4829class TimeStrToDate(Func):
4830    pass
4831
4832
4833class TimeStrToTime(Func):
4834    pass
4835
4836
4837class TimeStrToUnix(Func):
4838    pass
4839
4840
4841class Trim(Func):
4842    arg_types = {
4843        "this": True,
4844        "expression": False,
4845        "position": False,
4846        "collation": False,
4847    }
4848
4849
4850class TsOrDsAdd(Func, TimeUnit):
4851    arg_types = {"this": True, "expression": True, "unit": False}
4852
4853
4854class TsOrDsToDateStr(Func):
4855    pass
4856
4857
4858class TsOrDsToDate(Func):
4859    arg_types = {"this": True, "format": False}
4860
4861
4862class TsOrDiToDi(Func):
4863    pass
4864
4865
4866class Unhex(Func):
4867    pass
4868
4869
4870class UnixToStr(Func):
4871    arg_types = {"this": True, "format": False}
4872
4873
4874# https://prestodb.io/docs/current/functions/datetime.html
4875# presto has weird zone/hours/minutes
4876class UnixToTime(Func):
4877    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4878
4879    SECONDS = Literal.string("seconds")
4880    MILLIS = Literal.string("millis")
4881    MICROS = Literal.string("micros")
4882
4883
4884class UnixToTimeStr(Func):
4885    pass
4886
4887
4888class Upper(Func):
4889    _sql_names = ["UPPER", "UCASE"]
4890
4891
4892class Variance(AggFunc):
4893    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4894
4895
4896class VariancePop(AggFunc):
4897    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4898
4899
4900class Week(Func):
4901    arg_types = {"this": True, "mode": False}
4902
4903
4904class XMLTable(Func):
4905    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4906
4907
4908class Year(Func):
4909    pass
4910
4911
4912class Use(Expression):
4913    arg_types = {"this": True, "kind": False}
4914
4915
4916class Merge(Expression):
4917    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4918
4919
4920class When(Func):
4921    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4922
4923
4924# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
4925# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
4926class NextValueFor(Func):
4927    arg_types = {"this": True, "order": False}
4928
4929
4930def _norm_arg(arg):
4931    return arg.lower() if type(arg) is str else arg
4932
4933
4934ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4935
4936
4937# Helpers
4938@t.overload
4939def maybe_parse(
4940    sql_or_expression: ExpOrStr,
4941    *,
4942    into: t.Type[E],
4943    dialect: DialectType = None,
4944    prefix: t.Optional[str] = None,
4945    copy: bool = False,
4946    **opts,
4947) -> E:
4948    ...
4949
4950
4951@t.overload
4952def maybe_parse(
4953    sql_or_expression: str | E,
4954    *,
4955    into: t.Optional[IntoType] = None,
4956    dialect: DialectType = None,
4957    prefix: t.Optional[str] = None,
4958    copy: bool = False,
4959    **opts,
4960) -> E:
4961    ...
4962
4963
4964def maybe_parse(
4965    sql_or_expression: ExpOrStr,
4966    *,
4967    into: t.Optional[IntoType] = None,
4968    dialect: DialectType = None,
4969    prefix: t.Optional[str] = None,
4970    copy: bool = False,
4971    **opts,
4972) -> Expression:
4973    """Gracefully handle a possible string or expression.
4974
4975    Example:
4976        >>> maybe_parse("1")
4977        (LITERAL this: 1, is_string: False)
4978        >>> maybe_parse(to_identifier("x"))
4979        (IDENTIFIER this: x, quoted: False)
4980
4981    Args:
4982        sql_or_expression: the SQL code string or an expression
4983        into: the SQLGlot Expression to parse into
4984        dialect: the dialect used to parse the input expressions (in the case that an
4985            input expression is a SQL string).
4986        prefix: a string to prefix the sql with before it gets parsed
4987            (automatically includes a space)
4988        copy: whether or not to copy the expression.
4989        **opts: other options to use to parse the input expressions (again, in the case
4990            that an input expression is a SQL string).
4991
4992    Returns:
4993        Expression: the parsed or given expression.
4994    """
4995    if isinstance(sql_or_expression, Expression):
4996        if copy:
4997            return sql_or_expression.copy()
4998        return sql_or_expression
4999
5000    if sql_or_expression is None:
5001        raise ParseError(f"SQL cannot be None")
5002
5003    import sqlglot
5004
5005    sql = str(sql_or_expression)
5006    if prefix:
5007        sql = f"{prefix} {sql}"
5008
5009    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5010
5011
5012@t.overload
5013def maybe_copy(instance: None, copy: bool = True) -> None:
5014    ...
5015
5016
5017@t.overload
5018def maybe_copy(instance: E, copy: bool = True) -> E:
5019    ...
5020
5021
5022def maybe_copy(instance, copy=True):
5023    return instance.copy() if copy and instance else instance
5024
5025
5026def _is_wrong_expression(expression, into):
5027    return isinstance(expression, Expression) and not isinstance(expression, into)
5028
5029
5030def _apply_builder(
5031    expression,
5032    instance,
5033    arg,
5034    copy=True,
5035    prefix=None,
5036    into=None,
5037    dialect=None,
5038    **opts,
5039):
5040    if _is_wrong_expression(expression, into):
5041        expression = into(this=expression)
5042    instance = maybe_copy(instance, copy)
5043    expression = maybe_parse(
5044        sql_or_expression=expression,
5045        prefix=prefix,
5046        into=into,
5047        dialect=dialect,
5048        **opts,
5049    )
5050    instance.set(arg, expression)
5051    return instance
5052
5053
5054def _apply_child_list_builder(
5055    *expressions,
5056    instance,
5057    arg,
5058    append=True,
5059    copy=True,
5060    prefix=None,
5061    into=None,
5062    dialect=None,
5063    properties=None,
5064    **opts,
5065):
5066    instance = maybe_copy(instance, copy)
5067    parsed = []
5068    for expression in expressions:
5069        if expression is not None:
5070            if _is_wrong_expression(expression, into):
5071                expression = into(expressions=[expression])
5072
5073            expression = maybe_parse(
5074                expression,
5075                into=into,
5076                dialect=dialect,
5077                prefix=prefix,
5078                **opts,
5079            )
5080            parsed.extend(expression.expressions)
5081
5082    existing = instance.args.get(arg)
5083    if append and existing:
5084        parsed = existing.expressions + parsed
5085
5086    child = into(expressions=parsed)
5087    for k, v in (properties or {}).items():
5088        child.set(k, v)
5089    instance.set(arg, child)
5090
5091    return instance
5092
5093
5094def _apply_list_builder(
5095    *expressions,
5096    instance,
5097    arg,
5098    append=True,
5099    copy=True,
5100    prefix=None,
5101    into=None,
5102    dialect=None,
5103    **opts,
5104):
5105    inst = maybe_copy(instance, copy)
5106
5107    expressions = [
5108        maybe_parse(
5109            sql_or_expression=expression,
5110            into=into,
5111            prefix=prefix,
5112            dialect=dialect,
5113            **opts,
5114        )
5115        for expression in expressions
5116        if expression is not None
5117    ]
5118
5119    existing_expressions = inst.args.get(arg)
5120    if append and existing_expressions:
5121        expressions = existing_expressions + expressions
5122
5123    inst.set(arg, expressions)
5124    return inst
5125
5126
5127def _apply_conjunction_builder(
5128    *expressions,
5129    instance,
5130    arg,
5131    into=None,
5132    append=True,
5133    copy=True,
5134    dialect=None,
5135    **opts,
5136):
5137    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5138    if not expressions:
5139        return instance
5140
5141    inst = maybe_copy(instance, copy)
5142
5143    existing = inst.args.get(arg)
5144    if append and existing is not None:
5145        expressions = [existing.this if into else existing] + list(expressions)
5146
5147    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5148
5149    inst.set(arg, into(this=node) if into else node)
5150    return inst
5151
5152
5153def _apply_cte_builder(
5154    instance: E,
5155    alias: ExpOrStr,
5156    as_: ExpOrStr,
5157    recursive: t.Optional[bool] = None,
5158    append: bool = True,
5159    dialect: DialectType = None,
5160    copy: bool = True,
5161    **opts,
5162) -> E:
5163    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5164    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5165    cte = CTE(this=as_expression, alias=alias_expression)
5166    return _apply_child_list_builder(
5167        cte,
5168        instance=instance,
5169        arg="with",
5170        append=append,
5171        copy=copy,
5172        into=With,
5173        properties={"recursive": recursive or False},
5174    )
5175
5176
5177def _combine(
5178    expressions: t.Sequence[t.Optional[ExpOrStr]],
5179    operator: t.Type[Connector],
5180    dialect: DialectType = None,
5181    copy: bool = True,
5182    **opts,
5183) -> Expression:
5184    conditions = [
5185        condition(expression, dialect=dialect, copy=copy, **opts)
5186        for expression in expressions
5187        if expression is not None
5188    ]
5189
5190    this, *rest = conditions
5191    if rest:
5192        this = _wrap(this, Connector)
5193    for expression in rest:
5194        this = operator(this=this, expression=_wrap(expression, Connector))
5195
5196    return this
5197
5198
5199def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5200    return Paren(this=expression) if isinstance(expression, kind) else expression
5201
5202
5203def union(
5204    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5205) -> Union:
5206    """
5207    Initializes a syntax tree from one UNION expression.
5208
5209    Example:
5210        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5211        'SELECT * FROM foo UNION SELECT * FROM bla'
5212
5213    Args:
5214        left: the SQL code string corresponding to the left-hand side.
5215            If an `Expression` instance is passed, it will be used as-is.
5216        right: the SQL code string corresponding to the right-hand side.
5217            If an `Expression` instance is passed, it will be used as-is.
5218        distinct: set the DISTINCT flag if and only if this is true.
5219        dialect: the dialect used to parse the input expression.
5220        opts: other options to use to parse the input expressions.
5221
5222    Returns:
5223        The new Union instance.
5224    """
5225    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5226    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5227
5228    return Union(this=left, expression=right, distinct=distinct)
5229
5230
5231def intersect(
5232    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5233) -> Intersect:
5234    """
5235    Initializes a syntax tree from one INTERSECT expression.
5236
5237    Example:
5238        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5239        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5240
5241    Args:
5242        left: the SQL code string corresponding to the left-hand side.
5243            If an `Expression` instance is passed, it will be used as-is.
5244        right: the SQL code string corresponding to the right-hand side.
5245            If an `Expression` instance is passed, it will be used as-is.
5246        distinct: set the DISTINCT flag if and only if this is true.
5247        dialect: the dialect used to parse the input expression.
5248        opts: other options to use to parse the input expressions.
5249
5250    Returns:
5251        The new Intersect instance.
5252    """
5253    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5254    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5255
5256    return Intersect(this=left, expression=right, distinct=distinct)
5257
5258
5259def except_(
5260    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5261) -> Except:
5262    """
5263    Initializes a syntax tree from one EXCEPT expression.
5264
5265    Example:
5266        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5267        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5268
5269    Args:
5270        left: the SQL code string corresponding to the left-hand side.
5271            If an `Expression` instance is passed, it will be used as-is.
5272        right: the SQL code string corresponding to the right-hand side.
5273            If an `Expression` instance is passed, it will be used as-is.
5274        distinct: set the DISTINCT flag if and only if this is true.
5275        dialect: the dialect used to parse the input expression.
5276        opts: other options to use to parse the input expressions.
5277
5278    Returns:
5279        The new Except instance.
5280    """
5281    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5282    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5283
5284    return Except(this=left, expression=right, distinct=distinct)
5285
5286
5287def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5288    """
5289    Initializes a syntax tree from one or multiple SELECT expressions.
5290
5291    Example:
5292        >>> select("col1", "col2").from_("tbl").sql()
5293        'SELECT col1, col2 FROM tbl'
5294
5295    Args:
5296        *expressions: the SQL code string to parse as the expressions of a
5297            SELECT statement. If an Expression instance is passed, this is used as-is.
5298        dialect: the dialect used to parse the input expressions (in the case that an
5299            input expression is a SQL string).
5300        **opts: other options to use to parse the input expressions (again, in the case
5301            that an input expression is a SQL string).
5302
5303    Returns:
5304        Select: the syntax tree for the SELECT statement.
5305    """
5306    return Select().select(*expressions, dialect=dialect, **opts)
5307
5308
5309def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5310    """
5311    Initializes a syntax tree from a FROM expression.
5312
5313    Example:
5314        >>> from_("tbl").select("col1", "col2").sql()
5315        'SELECT col1, col2 FROM tbl'
5316
5317    Args:
5318        *expression: the SQL code string to parse as the FROM expressions of a
5319            SELECT statement. If an Expression instance is passed, this is used as-is.
5320        dialect: the dialect used to parse the input expression (in the case that the
5321            input expression is a SQL string).
5322        **opts: other options to use to parse the input expressions (again, in the case
5323            that the input expression is a SQL string).
5324
5325    Returns:
5326        Select: the syntax tree for the SELECT statement.
5327    """
5328    return Select().from_(expression, dialect=dialect, **opts)
5329
5330
5331def update(
5332    table: str | Table,
5333    properties: dict,
5334    where: t.Optional[ExpOrStr] = None,
5335    from_: t.Optional[ExpOrStr] = None,
5336    dialect: DialectType = None,
5337    **opts,
5338) -> Update:
5339    """
5340    Creates an update statement.
5341
5342    Example:
5343        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5344        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5345
5346    Args:
5347        *properties: dictionary of properties to set which are
5348            auto converted to sql objects eg None -> NULL
5349        where: sql conditional parsed into a WHERE statement
5350        from_: sql statement parsed into a FROM statement
5351        dialect: the dialect used to parse the input expressions.
5352        **opts: other options to use to parse the input expressions.
5353
5354    Returns:
5355        Update: the syntax tree for the UPDATE statement.
5356    """
5357    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5358    update_expr.set(
5359        "expressions",
5360        [
5361            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5362            for k, v in properties.items()
5363        ],
5364    )
5365    if from_:
5366        update_expr.set(
5367            "from",
5368            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5369        )
5370    if isinstance(where, Condition):
5371        where = Where(this=where)
5372    if where:
5373        update_expr.set(
5374            "where",
5375            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5376        )
5377    return update_expr
5378
5379
5380def delete(
5381    table: ExpOrStr,
5382    where: t.Optional[ExpOrStr] = None,
5383    returning: t.Optional[ExpOrStr] = None,
5384    dialect: DialectType = None,
5385    **opts,
5386) -> Delete:
5387    """
5388    Builds a delete statement.
5389
5390    Example:
5391        >>> delete("my_table", where="id > 1").sql()
5392        'DELETE FROM my_table WHERE id > 1'
5393
5394    Args:
5395        where: sql conditional parsed into a WHERE statement
5396        returning: sql conditional parsed into a RETURNING statement
5397        dialect: the dialect used to parse the input expressions.
5398        **opts: other options to use to parse the input expressions.
5399
5400    Returns:
5401        Delete: the syntax tree for the DELETE statement.
5402    """
5403    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5404    if where:
5405        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5406    if returning:
5407        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5408    return delete_expr
5409
5410
5411def insert(
5412    expression: ExpOrStr,
5413    into: ExpOrStr,
5414    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5415    overwrite: t.Optional[bool] = None,
5416    dialect: DialectType = None,
5417    copy: bool = True,
5418    **opts,
5419) -> Insert:
5420    """
5421    Builds an INSERT statement.
5422
5423    Example:
5424        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5425        'INSERT INTO tbl VALUES (1, 2, 3)'
5426
5427    Args:
5428        expression: the sql string or expression of the INSERT statement
5429        into: the tbl to insert data to.
5430        columns: optionally the table's column names.
5431        overwrite: whether to INSERT OVERWRITE or not.
5432        dialect: the dialect used to parse the input expressions.
5433        copy: whether or not to copy the expression.
5434        **opts: other options to use to parse the input expressions.
5435
5436    Returns:
5437        Insert: the syntax tree for the INSERT statement.
5438    """
5439    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5440    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5441
5442    if columns:
5443        this = _apply_list_builder(
5444            *columns,
5445            instance=Schema(this=this),
5446            arg="expressions",
5447            into=Identifier,
5448            copy=False,
5449            dialect=dialect,
5450            **opts,
5451        )
5452
5453    return Insert(this=this, expression=expr, overwrite=overwrite)
5454
5455
5456def condition(
5457    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5458) -> Condition:
5459    """
5460    Initialize a logical condition expression.
5461
5462    Example:
5463        >>> condition("x=1").sql()
5464        'x = 1'
5465
5466        This is helpful for composing larger logical syntax trees:
5467        >>> where = condition("x=1")
5468        >>> where = where.and_("y=1")
5469        >>> Select().from_("tbl").select("*").where(where).sql()
5470        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5471
5472    Args:
5473        *expression: the SQL code string to parse.
5474            If an Expression instance is passed, this is used as-is.
5475        dialect: the dialect used to parse the input expression (in the case that the
5476            input expression is a SQL string).
5477        copy: Whether or not to copy `expression` (only applies to expressions).
5478        **opts: other options to use to parse the input expressions (again, in the case
5479            that the input expression is a SQL string).
5480
5481    Returns:
5482        The new Condition instance
5483    """
5484    return maybe_parse(
5485        expression,
5486        into=Condition,
5487        dialect=dialect,
5488        copy=copy,
5489        **opts,
5490    )
5491
5492
5493def and_(
5494    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5495) -> Condition:
5496    """
5497    Combine multiple conditions with an AND logical operator.
5498
5499    Example:
5500        >>> and_("x=1", and_("y=1", "z=1")).sql()
5501        'x = 1 AND (y = 1 AND z = 1)'
5502
5503    Args:
5504        *expressions: the SQL code strings to parse.
5505            If an Expression instance is passed, this is used as-is.
5506        dialect: the dialect used to parse the input expression.
5507        copy: whether or not to copy `expressions` (only applies to Expressions).
5508        **opts: other options to use to parse the input expressions.
5509
5510    Returns:
5511        And: the new condition
5512    """
5513    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5514
5515
5516def or_(
5517    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5518) -> Condition:
5519    """
5520    Combine multiple conditions with an OR logical operator.
5521
5522    Example:
5523        >>> or_("x=1", or_("y=1", "z=1")).sql()
5524        'x = 1 OR (y = 1 OR z = 1)'
5525
5526    Args:
5527        *expressions: the SQL code strings to parse.
5528            If an Expression instance is passed, this is used as-is.
5529        dialect: the dialect used to parse the input expression.
5530        copy: whether or not to copy `expressions` (only applies to Expressions).
5531        **opts: other options to use to parse the input expressions.
5532
5533    Returns:
5534        Or: the new condition
5535    """
5536    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5537
5538
5539def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5540    """
5541    Wrap a condition with a NOT operator.
5542
5543    Example:
5544        >>> not_("this_suit='black'").sql()
5545        "NOT this_suit = 'black'"
5546
5547    Args:
5548        expression: the SQL code string to parse.
5549            If an Expression instance is passed, this is used as-is.
5550        dialect: the dialect used to parse the input expression.
5551        copy: whether to copy the expression or not.
5552        **opts: other options to use to parse the input expressions.
5553
5554    Returns:
5555        The new condition.
5556    """
5557    this = condition(
5558        expression,
5559        dialect=dialect,
5560        copy=copy,
5561        **opts,
5562    )
5563    return Not(this=_wrap(this, Connector))
5564
5565
5566def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5567    """
5568    Wrap an expression in parentheses.
5569
5570    Example:
5571        >>> paren("5 + 3").sql()
5572        '(5 + 3)'
5573
5574    Args:
5575        expression: the SQL code string to parse.
5576            If an Expression instance is passed, this is used as-is.
5577        copy: whether to copy the expression or not.
5578
5579    Returns:
5580        The wrapped expression.
5581    """
5582    return Paren(this=maybe_parse(expression, copy=copy))
5583
5584
5585SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5586
5587
5588@t.overload
5589def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5590    ...
5591
5592
5593@t.overload
5594def to_identifier(
5595    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5596) -> Identifier:
5597    ...
5598
5599
5600def to_identifier(name, quoted=None, copy=True):
5601    """Builds an identifier.
5602
5603    Args:
5604        name: The name to turn into an identifier.
5605        quoted: Whether or not force quote the identifier.
5606        copy: Whether or not to copy a passed in Identefier node.
5607
5608    Returns:
5609        The identifier ast node.
5610    """
5611
5612    if name is None:
5613        return None
5614
5615    if isinstance(name, Identifier):
5616        identifier = maybe_copy(name, copy)
5617    elif isinstance(name, str):
5618        identifier = Identifier(
5619            this=name,
5620            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5621        )
5622    else:
5623        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5624    return identifier
5625
5626
5627INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5628
5629
5630def to_interval(interval: str | Literal) -> Interval:
5631    """Builds an interval expression from a string like '1 day' or '5 months'."""
5632    if isinstance(interval, Literal):
5633        if not interval.is_string:
5634            raise ValueError("Invalid interval string.")
5635
5636        interval = interval.this
5637
5638    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5639
5640    if not interval_parts:
5641        raise ValueError("Invalid interval string.")
5642
5643    return Interval(
5644        this=Literal.string(interval_parts.group(1)),
5645        unit=Var(this=interval_parts.group(2)),
5646    )
5647
5648
5649@t.overload
5650def to_table(sql_path: str | Table, **kwargs) -> Table:
5651    ...
5652
5653
5654@t.overload
5655def to_table(sql_path: None, **kwargs) -> None:
5656    ...
5657
5658
5659def to_table(
5660    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5661) -> t.Optional[Table]:
5662    """
5663    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5664    If a table is passed in then that table is returned.
5665
5666    Args:
5667        sql_path: a `[catalog].[schema].[table]` string.
5668        dialect: the source dialect according to which the table name will be parsed.
5669        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5670
5671    Returns:
5672        A table expression.
5673    """
5674    if sql_path is None or isinstance(sql_path, Table):
5675        return sql_path
5676    if not isinstance(sql_path, str):
5677        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5678
5679    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5680    if table:
5681        for k, v in kwargs.items():
5682            table.set(k, v)
5683
5684    return table
5685
5686
5687def to_column(sql_path: str | Column, **kwargs) -> Column:
5688    """
5689    Create a column from a `[table].[column]` sql path. Schema is optional.
5690
5691    If a column is passed in then that column is returned.
5692
5693    Args:
5694        sql_path: `[table].[column]` string
5695    Returns:
5696        Table: A column expression
5697    """
5698    if sql_path is None or isinstance(sql_path, Column):
5699        return sql_path
5700    if not isinstance(sql_path, str):
5701        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5702    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5703
5704
5705def alias_(
5706    expression: ExpOrStr,
5707    alias: str | Identifier,
5708    table: bool | t.Sequence[str | Identifier] = False,
5709    quoted: t.Optional[bool] = None,
5710    dialect: DialectType = None,
5711    copy: bool = True,
5712    **opts,
5713):
5714    """Create an Alias expression.
5715
5716    Example:
5717        >>> alias_('foo', 'bar').sql()
5718        'foo AS bar'
5719
5720        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5721        '(SELECT 1, 2) AS bar(a, b)'
5722
5723    Args:
5724        expression: the SQL code strings to parse.
5725            If an Expression instance is passed, this is used as-is.
5726        alias: the alias name to use. If the name has
5727            special characters it is quoted.
5728        table: Whether or not to create a table alias, can also be a list of columns.
5729        quoted: whether or not to quote the alias
5730        dialect: the dialect used to parse the input expression.
5731        copy: Whether or not to copy the expression.
5732        **opts: other options to use to parse the input expressions.
5733
5734    Returns:
5735        Alias: the aliased expression
5736    """
5737    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5738    alias = to_identifier(alias, quoted=quoted)
5739
5740    if table:
5741        table_alias = TableAlias(this=alias)
5742        exp.set("alias", table_alias)
5743
5744        if not isinstance(table, bool):
5745            for column in table:
5746                table_alias.append("columns", to_identifier(column, quoted=quoted))
5747
5748        return exp
5749
5750    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5751    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5752    # for the complete Window expression.
5753    #
5754    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5755
5756    if "alias" in exp.arg_types and not isinstance(exp, Window):
5757        exp.set("alias", alias)
5758        return exp
5759    return Alias(this=exp, alias=alias)
5760
5761
5762def subquery(
5763    expression: ExpOrStr,
5764    alias: t.Optional[Identifier | str] = None,
5765    dialect: DialectType = None,
5766    **opts,
5767) -> Select:
5768    """
5769    Build a subquery expression.
5770
5771    Example:
5772        >>> subquery('select x from tbl', 'bar').select('x').sql()
5773        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5774
5775    Args:
5776        expression: the SQL code strings to parse.
5777            If an Expression instance is passed, this is used as-is.
5778        alias: the alias name to use.
5779        dialect: the dialect used to parse the input expression.
5780        **opts: other options to use to parse the input expressions.
5781
5782    Returns:
5783        A new Select instance with the subquery expression included.
5784    """
5785
5786    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5787    return Select().from_(expression, dialect=dialect, **opts)
5788
5789
5790def column(
5791    col: str | Identifier,
5792    table: t.Optional[str | Identifier] = None,
5793    db: t.Optional[str | Identifier] = None,
5794    catalog: t.Optional[str | Identifier] = None,
5795    quoted: t.Optional[bool] = None,
5796) -> Column:
5797    """
5798    Build a Column.
5799
5800    Args:
5801        col: Column name.
5802        table: Table name.
5803        db: Database name.
5804        catalog: Catalog name.
5805        quoted: Whether to force quotes on the column's identifiers.
5806
5807    Returns:
5808        The new Column instance.
5809    """
5810    return Column(
5811        this=to_identifier(col, quoted=quoted),
5812        table=to_identifier(table, quoted=quoted),
5813        db=to_identifier(db, quoted=quoted),
5814        catalog=to_identifier(catalog, quoted=quoted),
5815    )
5816
5817
5818def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
5819    """Cast an expression to a data type.
5820
5821    Example:
5822        >>> cast('x + 1', 'int').sql()
5823        'CAST(x + 1 AS INT)'
5824
5825    Args:
5826        expression: The expression to cast.
5827        to: The datatype to cast to.
5828
5829    Returns:
5830        The new Cast instance.
5831    """
5832    expression = maybe_parse(expression, **opts)
5833    return Cast(this=expression, to=DataType.build(to, **opts))
5834
5835
5836def table_(
5837    table: Identifier | str,
5838    db: t.Optional[Identifier | str] = None,
5839    catalog: t.Optional[Identifier | str] = None,
5840    quoted: t.Optional[bool] = None,
5841    alias: t.Optional[Identifier | str] = None,
5842) -> Table:
5843    """Build a Table.
5844
5845    Args:
5846        table: Table name.
5847        db: Database name.
5848        catalog: Catalog name.
5849        quote: Whether to force quotes on the table's identifiers.
5850        alias: Table's alias.
5851
5852    Returns:
5853        The new Table instance.
5854    """
5855    return Table(
5856        this=to_identifier(table, quoted=quoted),
5857        db=to_identifier(db, quoted=quoted),
5858        catalog=to_identifier(catalog, quoted=quoted),
5859        alias=TableAlias(this=to_identifier(alias)) if alias else None,
5860    )
5861
5862
5863def values(
5864    values: t.Iterable[t.Tuple[t.Any, ...]],
5865    alias: t.Optional[str] = None,
5866    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
5867) -> Values:
5868    """Build VALUES statement.
5869
5870    Example:
5871        >>> values([(1, '2')]).sql()
5872        "VALUES (1, '2')"
5873
5874    Args:
5875        values: values statements that will be converted to SQL
5876        alias: optional alias
5877        columns: Optional list of ordered column names or ordered dictionary of column names to types.
5878         If either are provided then an alias is also required.
5879
5880    Returns:
5881        Values: the Values expression object
5882    """
5883    if columns and not alias:
5884        raise ValueError("Alias is required when providing columns")
5885
5886    return Values(
5887        expressions=[convert(tup) for tup in values],
5888        alias=(
5889            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
5890            if columns
5891            else (TableAlias(this=to_identifier(alias)) if alias else None)
5892        ),
5893    )
5894
5895
5896def var(name: t.Optional[ExpOrStr]) -> Var:
5897    """Build a SQL variable.
5898
5899    Example:
5900        >>> repr(var('x'))
5901        '(VAR this: x)'
5902
5903        >>> repr(var(column('x', table='y')))
5904        '(VAR this: x)'
5905
5906    Args:
5907        name: The name of the var or an expression who's name will become the var.
5908
5909    Returns:
5910        The new variable node.
5911    """
5912    if not name:
5913        raise ValueError("Cannot convert empty name into var.")
5914
5915    if isinstance(name, Expression):
5916        name = name.name
5917    return Var(this=name)
5918
5919
5920def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
5921    """Build ALTER TABLE... RENAME... expression
5922
5923    Args:
5924        old_name: The old name of the table
5925        new_name: The new name of the table
5926
5927    Returns:
5928        Alter table expression
5929    """
5930    old_table = to_table(old_name)
5931    new_table = to_table(new_name)
5932    return AlterTable(
5933        this=old_table,
5934        actions=[
5935            RenameTable(this=new_table),
5936        ],
5937    )
5938
5939
5940def convert(value: t.Any, copy: bool = False) -> Expression:
5941    """Convert a python value into an expression object.
5942
5943    Raises an error if a conversion is not possible.
5944
5945    Args:
5946        value: A python object.
5947        copy: Whether or not to copy `value` (only applies to Expressions and collections).
5948
5949    Returns:
5950        Expression: the equivalent expression object.
5951    """
5952    if isinstance(value, Expression):
5953        return maybe_copy(value, copy)
5954    if isinstance(value, str):
5955        return Literal.string(value)
5956    if isinstance(value, bool):
5957        return Boolean(this=value)
5958    if value is None or (isinstance(value, float) and math.isnan(value)):
5959        return NULL
5960    if isinstance(value, numbers.Number):
5961        return Literal.number(value)
5962    if isinstance(value, datetime.datetime):
5963        datetime_literal = Literal.string(
5964            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
5965        )
5966        return TimeStrToTime(this=datetime_literal)
5967    if isinstance(value, datetime.date):
5968        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
5969        return DateStrToDate(this=date_literal)
5970    if isinstance(value, tuple):
5971        return Tuple(expressions=[convert(v, copy=copy) for v in value])
5972    if isinstance(value, list):
5973        return Array(expressions=[convert(v, copy=copy) for v in value])
5974    if isinstance(value, dict):
5975        return Map(
5976            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
5977            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
5978        )
5979    raise ValueError(f"Cannot convert {value}")
5980
5981
5982def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
5983    """
5984    Replace children of an expression with the result of a lambda fun(child) -> exp.
5985    """
5986    for k, v in expression.args.items():
5987        is_list_arg = type(v) is list
5988
5989        child_nodes = v if is_list_arg else [v]
5990        new_child_nodes = []
5991
5992        for cn in child_nodes:
5993            if isinstance(cn, Expression):
5994                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
5995                    new_child_nodes.append(child_node)
5996                    child_node.parent = expression
5997                    child_node.arg_key = k
5998            else:
5999                new_child_nodes.append(cn)
6000
6001        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6002
6003
6004def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6005    """
6006    Return all table names referenced through columns in an expression.
6007
6008    Example:
6009        >>> import sqlglot
6010        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6011        ['a', 'c']
6012
6013    Args:
6014        expression: expression to find table names.
6015        exclude: a table name to exclude
6016
6017    Returns:
6018        A list of unique names.
6019    """
6020    return {
6021        table
6022        for table in (column.table for column in expression.find_all(Column))
6023        if table and table != exclude
6024    }
6025
6026
6027def table_name(table: Table | str, dialect: DialectType = None) -> str:
6028    """Get the full name of a table as a string.
6029
6030    Args:
6031        table: Table expression node or string.
6032        dialect: The dialect to generate the table name for.
6033
6034    Examples:
6035        >>> from sqlglot import exp, parse_one
6036        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6037        'a.b.c'
6038
6039    Returns:
6040        The table name.
6041    """
6042
6043    table = maybe_parse(table, into=Table)
6044
6045    if not table:
6046        raise ValueError(f"Cannot parse {table}")
6047
6048    return ".".join(
6049        part.sql(dialect=dialect, identify=True)
6050        if not SAFE_IDENTIFIER_RE.match(part.name)
6051        else part.name
6052        for part in table.parts
6053    )
6054
6055
6056def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6057    """Replace all tables in expression according to the mapping.
6058
6059    Args:
6060        expression: expression node to be transformed and replaced.
6061        mapping: mapping of table names.
6062        copy: whether or not to copy the expression.
6063
6064    Examples:
6065        >>> from sqlglot import exp, parse_one
6066        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6067        'SELECT * FROM c'
6068
6069    Returns:
6070        The mapped expression.
6071    """
6072
6073    def _replace_tables(node: Expression) -> Expression:
6074        if isinstance(node, Table):
6075            new_name = mapping.get(table_name(node))
6076            if new_name:
6077                return to_table(
6078                    new_name,
6079                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6080                )
6081        return node
6082
6083    return expression.transform(_replace_tables, copy=copy)
6084
6085
6086def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6087    """Replace placeholders in an expression.
6088
6089    Args:
6090        expression: expression node to be transformed and replaced.
6091        args: positional names that will substitute unnamed placeholders in the given order.
6092        kwargs: keyword arguments that will substitute named placeholders.
6093
6094    Examples:
6095        >>> from sqlglot import exp, parse_one
6096        >>> replace_placeholders(
6097        ...     parse_one("select * from :tbl where ? = ?"),
6098        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6099        ... ).sql()
6100        "SELECT * FROM foo WHERE str_col = 'b'"
6101
6102    Returns:
6103        The mapped expression.
6104    """
6105
6106    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6107        if isinstance(node, Placeholder):
6108            if node.name:
6109                new_name = kwargs.get(node.name)
6110                if new_name:
6111                    return convert(new_name)
6112            else:
6113                try:
6114                    return convert(next(args))
6115                except StopIteration:
6116                    pass
6117        return node
6118
6119    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6120
6121
6122def expand(
6123    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6124) -> Expression:
6125    """Transforms an expression by expanding all referenced sources into subqueries.
6126
6127    Examples:
6128        >>> from sqlglot import parse_one
6129        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6130        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6131
6132        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6133        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6134
6135    Args:
6136        expression: The expression to expand.
6137        sources: A dictionary of name to Subqueryables.
6138        copy: Whether or not to copy the expression during transformation. Defaults to True.
6139
6140    Returns:
6141        The transformed expression.
6142    """
6143
6144    def _expand(node: Expression):
6145        if isinstance(node, Table):
6146            name = table_name(node)
6147            source = sources.get(name)
6148            if source:
6149                subquery = source.subquery(node.alias or name)
6150                subquery.comments = [f"source: {name}"]
6151                return subquery.transform(_expand, copy=False)
6152        return node
6153
6154    return expression.transform(_expand, copy=copy)
6155
6156
6157def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6158    """
6159    Returns a Func expression.
6160
6161    Examples:
6162        >>> func("abs", 5).sql()
6163        'ABS(5)'
6164
6165        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6166        'CAST(5 AS DOUBLE)'
6167
6168    Args:
6169        name: the name of the function to build.
6170        args: the args used to instantiate the function of interest.
6171        dialect: the source dialect.
6172        kwargs: the kwargs used to instantiate the function of interest.
6173
6174    Note:
6175        The arguments `args` and `kwargs` are mutually exclusive.
6176
6177    Returns:
6178        An instance of the function of interest, or an anonymous function, if `name` doesn't
6179        correspond to an existing `sqlglot.expressions.Func` class.
6180    """
6181    if args and kwargs:
6182        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6183
6184    from sqlglot.dialects.dialect import Dialect
6185
6186    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6187    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6188
6189    parser = Dialect.get_or_raise(dialect)().parser()
6190    from_args_list = parser.FUNCTIONS.get(name.upper())
6191
6192    if from_args_list:
6193        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6194    else:
6195        kwargs = kwargs or {"expressions": converted}
6196        function = Anonymous(this=name, **kwargs)
6197
6198    for error_message in function.error_messages(converted):
6199        raise ValueError(error_message)
6200
6201    return function
6202
6203
6204def true() -> Boolean:
6205    """
6206    Returns a true Boolean expression.
6207    """
6208    return Boolean(this=True)
6209
6210
6211def false() -> Boolean:
6212    """
6213    Returns a false Boolean expression.
6214    """
6215    return Boolean(this=False)
6216
6217
6218def null() -> Null:
6219    """
6220    Returns a Null expression.
6221    """
6222    return Null()
6223
6224
6225# TODO: deprecate this
6226TRUE = Boolean(this=True)
6227FALSE = Boolean(this=False)
6228NULL = Null()